Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-02-24
17
Medium Priority
?
3,204 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

876 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