Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3490
  • Last Modified:

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

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
bearblack
Asked:
bearblack
  • 7
  • 6
  • 4
3 Solutions
 
SiddharthRoutCommented:
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
 
bearblackAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
You can reference the trendline format like this:

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

Kevin
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zorvek (Kevin Jones)ConsultantCommented:
Correction and more detail:

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

Kevin
1
 
bearblackAuthor Commented:
That works except for the line color needs to be red.
0
 
SiddharthRoutCommented:
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
 
bearblackAuthor Commented:
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
 
SiddharthRoutCommented:
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
 
SiddharthRoutCommented:
My Apologies. Change 5 to 3 in the above code.

Sid
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
bearblackAuthor Commented:
Worked -- Had to Change color index to 3 to get red.
 
Is there a listing anywhere of the colors refelected by the colorindex.
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
SiddharthRoutCommented:
0
 
bearblackAuthor Commented:
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
 
SiddharthRoutCommented:
Gr8 :)

Sid
0
 
bearblackAuthor Commented:
Thanks to you both
0
 
bearblackAuthor Commented:
Combined both solutions into one
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now