Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do you format a Trend line in Excel using VBA?

Posted on 2011-02-24
17
Medium Priority
?
3,074 Views
Last Modified: 2012-05-11
I was able to create a trend with the series but I now need to format the trend line. Attached is a sample of the code I used to create the trendline.
Set shp = ActiveSheet.Shapes.AddChart
        With shp.Chart
            .SetSourceData Source:=Range("Week_Table")
            .ChartType = xlColumnClustered
            .ApplyLayout (3)
            .SeriesCollection(1).Trendlines.Add
            With .Parent
        ' name the chartobject
                .Name = "Week_Chart"
       ' then position it
                .Top = Range("D6").Top
                .Left = Range("D6").Left
            End With
        End With

Open in new window

0
Comment
Question by:bearblack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34973840
There are so many things that you can do with the trend line? What kind of formatting do you want to do?

I would suggest record a macro and perform the necessary operation on the trend line and once done, stop the macro and edit the code to suit your needs.

Which Excel version are you using?

Sid
0
 
LVL 2

Author Comment

by:bearblack
ID: 34974040
Recording does not work on trends in 2007 it does not add formatting steps in the visual basic window at least on my version it doesn't.

I want the trend line bold, large arrow point at end, and red.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34974118
You can reference the trendline format like this:

         With .SeriesCollection(1).Trendlines(1)
            .BeginArrowheadStyle = msoArrowheadTriangle
            ...
         End With

Kevin
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34974154
Correction and more detail:

         With .SeriesCollection(1).Trendlines(1).Format.Line
            .BeginArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadStyle = msoArrowheadTriangle
            .Weight = 3
            .ForeColor.SchemeColor = 10
         End With

Kevin
0
 
LVL 2

Author Comment

by:bearblack
ID: 34974189
That works except for the line color needs to be red.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34974202
Late for the party :)

>> 2007 it does not add formatting steps

That was why I asked you the Excel version.

Use this

    .Border.ColorIndex = 3 '<~~ Color Red
    .Border.Weight = xlThick <~~ To make it appear bold

Sid
0
 
LVL 2

Author Comment

by:bearblack
ID: 34974375
that soen't work when I added it

        With shp.Chart.SeriesCollection(1).Trendlines(1).Format.Line
            .EndArrowheadStyle = msoArrowheadTriangle
            .Weight = 3
            .Border.ColorIndex = 3
            .Border.Weight = xlThick
         End With
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1200 total points
ID: 34974394
Try this

With ActiveChart.SeriesCollection(1).Trendlines(1)
    .Border.ColorIndex = 5
    .Border.Weight = xlThick
    With .Format.Line
        .EndArrowheadStyle = msoArrowheadTriangle
    End With
End With

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34974425
My Apologies. Change 5 to 3 in the above code.

Sid
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 800 total points
ID: 34974440
It has to be done in the correct order:

        With .SeriesCollection(1).Trendlines(1)
            .Border.ColorIndex = 3
        End With
         With .SeriesCollection(1).Trendlines(1).Format.Line
            .BeginArrowheadStyle = msoArrowheadTriangle
            .EndArrowheadStyle = msoArrowheadTriangle
            .Weight = 3
        End With

Kevin
0
 
LVL 2

Author Comment

by:bearblack
ID: 34974466
Worked -- Had to Change color index to 3 to get red.
 
Is there a listing anywhere of the colors refelected by the colorindex.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 34974475
The enumerated constants below define the Excel colors as displayed on the color pallet. To use place the code below in any general code module. To invoke the VBE auto complete feature, while entering code in the VBE enter the type name (tColorIndex) followed by a period to display a list of all of the colors.

Public Enum tColorIndex
   ' Colors are in the same order as on the color pallet, down and across
   mxlAutomaticColor = 0
   mxlNoColor = -4142
   mxlBlack = 1
   mxlDarkRed = 9
   mxlRed = 3
   mxlPink = 7
   mxlRose = 38
   mxlBrown = 53
   mxlOrange = 46
   mxlLightOrange = 45
   mxlGold = 44
   mxlTan = 40
   mxlOliveGreen = 52
   mxlDarkYellow = 12
   mxlLime = 43
   mxlYellow = 6
   mxlLightYellow = 36
   mxlDarkGreen = 51
   mxlGreen = 10
   mxlSeaGreen = 50
   mxlBrightGreen = 4
   mxlLightGreen = 35
   mxlDarkTeal = 49
   mxlTeal = 14
   mxlAqua = 42
   mxlTurquoise = 8
   mxlLightTurquoise = 34
   mxlDarkBlue = 11
   mxlBlue = 5
   mxlLightBlue = 41
   mxlSkyBlue = 33
   mxlPaleBlue = 37
   mxlIndigo = 55
   mxlBlueGray = 47
   mxlViolet = 13
   mxlPlum = 54
   mxlLavender = 39
   mxlGray80 = 56
   mxlGray50 = 16
   mxlGray40 = 48
   mxlGray25 = 15
   mxlWhite = 2
   ' Chart Fill colors as shown on the color palatte
   mxlChartFillPastelBlue = 17
   mxlChartFillPlum = 18
   mxlChartFillLightTan = 19
   mxlChartFillLightTurquoise = 20
   mxlChartFillDarkViolet = 21
   mxlChartFillPastelPink = 22
   mxlChartFillDarkerLightBlue = 23
   mxlChartFillLightBueGray = 24
   ' Chart Line colors as shown on the color palatte
   mxlChartLineDarkBlue = 25
   mxlChartLinePink = 26
   mxlChartLineYellow = 27
   mxlChartLineTurquoise = 28
   mxlChartLineViolet = 29
   mxlChartLineDarkRed = 30
   mxlChartLineTeal = 31
   mxlChartLineBlue = 32
   ' Shape scheme colors - these color indices are used by Excel shapes and are in the same order as the basic color set
   mxlSchemeColorBlack = 8
   mxlSchemeColorDarkRed = 16
   mxlSchemeColorRed = 10
   mxlSchemeColorPink = 14
   mxlSchemeColorRose = 45
   mxlSchemeColorBrown = 60
   mxlSchemeColorOrange = 53
   mxlSchemeColorLightOrange = 52
   mxlSchemeColorGold = 51
   mxlSchemeColorTan = 47
   mxlSchemeColorOliveGreen = 59
   mxlSchemeColorDarkYellow = 19
   mxlSchemeColorLime = 50
   mxlSchemeColorYellow = 13
   mxlSchemeColorLightYellow = 43
   mxlSchemeColorDarkGreen = 58
   mxlSchemeColorGreen = 17
   mxlSchemeColorSeaGreen = 57
   mxlSchemeColorBrightGreen = 11
   mxlSchemeColorLightGreen = 42
   mxlSchemeColorDarkTeal = 56
   mxlSchemeColorTeal = 21
   mxlSchemeColorAqua = 49
   mxlSchemeColorTurquoise = 15
   mxlSchemeColorLightTurquoise = 41
   mxlSchemeColorDarkBlue = 18
   mxlSchemeColorBlue = 12
   mxlSchemeColorLightBlue = 48
   mxlSchemeColorSkyBlue = 40
   mxlSchemeColorPaleBlue = 44
   mxlSchemeColorIndigo = 62
   mxlSchemeColorBlueGray = 54
   mxlSchemeColorViolet = 20
   mxlSchemeColorPlum = 61
   mxlSchemeColorLavender = 46
   mxlSchemeColorGray80 = 63
   mxlSchemeColorGray50 = 23
   mxlSchemeColorGray40 = 55
   mxlSchemeColorGray25 = 22
   mxlSchemeColorWhite = 9
End Enum

Kevin
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34974478
0
 
LVL 2

Assisted Solution

by:bearblack
bearblack earned 0 total points
ID: 34974488
The below worked:

   Set shp = ActiveSheet.Shapes.AddChart
        With shp.Chart
            .SetSourceData Source:=Range("Week_Table")
            .ChartType = xlColumnClustered
            .ApplyLayout (3)
            .SeriesCollection(1).Trendlines.Add
            With .Parent
                .Name = "Week_Chart"
                .Top = Range("D6").Top
                .Left = Range("D6").Left
            End With
        End With
        With shp.Chart.SeriesCollection(1).Trendlines(1)
            .Border.ColorIndex = 3
            .Border.Weight = xlThick
                With .Format.Line
                    .EndArrowheadStyle = msoArrowheadTriangle
                End With
        End With
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34974496
Gr8 :)

Sid
0
 
LVL 2

Author Comment

by:bearblack
ID: 34974638
Thanks to you both
0
 
LVL 2

Author Closing Comment

by:bearblack
ID: 35005342
Combined both solutions into one
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question