Solved

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

Posted on 2011-02-24
17
2,899 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 300 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 200 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

728 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