Solved

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

Posted on 2011-02-24
17
2,948 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

628 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