Trendlines VBA for All Charts on a Worksheet (ActiveSheet)

[Mike808]

So, a great user on this forum posted in May of 2016 this great code for adding trendlines to a chart using VBA and looping to add the trendlines for all series applicable. My question is, how would I amend this code in the regular Module to make the trendlines occur on all four of my charts on the ActiveSheet? I know it is some kind of array or Dim/Set for the charts, but I can't figure it out!! For the code below....thanks much to user Chihiro:

It would be something like below in regular Module:

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection

Set mySeriesCol = ActiveSheet.ChartObjects(1).Chart.SeriesCollection

For i = 1 To mySeriesCol.Count
    If mySeriesCol(i).Trendlines.Count > 0 Then
    Else
        mySeriesCol(i).Trendlines.Add
    End If
Next
End Sub

And then in Sheet2(Chart) module add:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
End Sub

This will add linear trend line to each series. Code also checks if there is trend line already present on the series. If it is, it will not add another.

 

[Hui]

Try this:

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection
Dim sh As Worksheet
Dim i As Long, j As Integer

For Each sh In Worksheets
  If sh.ChartObjects.Count > 0 Then
  For i = 1 To sh.ChartObjects.Count
  Debug.Print sh.ChartObjects(i).Chart.Name
   
  Set mySeriesCol = sh.ChartObjects(i).Chart.SeriesCollection
 
  For j = 1 To mySeriesCol.Count
  If mySeriesCol(j).Trendlines.Count > 0 Then
  Else
  mySeriesCol(j).Trendlines.Add
   
  mySeriesCol(j).Trendlines(1).Select
  With Selection.Format.Line
  .Visible = msoTrue
  .ForeColor.RGB = RGB(255, 0, 0) 'Color Red
  End With
  With Selection.Format.Line
  .Visible = msoTrue
  .Weight = 1.75
  .DashStyle = msoLineDash
  End With
  End If
  Next j
   
  Next i
  End If
Next sh

End Sub

 

[Mike808]

Hui....Perfect Work!!! I see you got fancy with the lines and grids.....I appreciate it and I love that I can learn from this code to elaborate on my own.

 

[Hui]

I was pretty sure that you didn't want the default Trendline

 

[Mike808]

Hui, it definitely gave me a visual option I didn't think about. I am now assigning to the button I made on the chart sheet. I was just going to use the Record Macro feature to clear all of the trendlines off of the charts. Also trying to start a code to do it and experiment.

 

[Mr. C]

Hui,
I have similar situation with multiple charts on multiple worksheets. All charts show same 3 series for different data. Trying to modify your code to have each series trendline with a different color. Can you offer a solution? I can change the trendline formatting but can't figure out what to change in the code.
Thanks!

 

[Hui]

Hui,
I have similar situation with multiple charts on multiple worksheets. All charts show same 3 series for different data. Trying to modify your code to have each series trendline with a different color. Can you offer a solution? I can change the trendline formatting but can't figure out what to change in the code.
Thanks!

Can you please post a sample file

 

[Mr. C]

Hui,
Thank you in advance for taking a look at my file. I have your original code in Module 1 which worked fine. Module 2 is what I came up with to remove the trendlines from all charts and Module 3 is adding trendlines to all charts as a solid line in the same color as the data line. Ideally I'd like to combine my two modules to work like yours does. Just not skilled enough at this point to figure it out.
Thanks,
Michael

 

[Hui]

Hows this?

Sub LoopThroughCharts()
'Loop through every graph in the active workbook and remove trendlines
'Have to run this twice to remove all trendlines
'First run removes trendlines from one chart
'Second run removes trendlines from all remaining charts

'This code works!!! Deletes trendlines from all charts in workbook
'Now to figure out how to only run once to do the job

Dim sht As Worksheet
Dim CurrentSheet As Worksheet
Dim cht As ChartObject

Application.ScreenUpdating = False
Application.EnableEvents = False

Set CurrentSheet = ActiveSheet

' Loop thru each worksheet
For Each sht In ActiveWorkbook.Worksheets
   
   ' Loop thru each chart
   For Each cht In sht.ChartObjects
   cht.Activate
      
      ' Loop thru each Series
      'On Error Resume Next
      For Each srchart In ActiveChart.SeriesCollection
         
         ' Loop thru each Series
         If srchart.Trendlines.Count > 0 Then
            For Each tltrendline In srchart.Trendlines
               ' Delete trendline if it exists
               tltrendline.Delete
            Next tltrendline
         Else
            ' Add Trendline
            srchart.Trendlines.Add(Type:=xlLinear, Forward:=0, _
               Backward:=0, DisplayEquation:=False, DisplayRSquared:=False).Select
            
            With Selection.Format.Line
               .Visible = msoTrue
               '.ForeColor.RGB = RGB(68, 114, 196) 'Color Blue
               .ForeColor.RGB = srchart.Format.Line.ForeColor.RGB 'Color as per series
               .Weight = 1.75
               .DashStyle = msoLineSolid
            End With
         End If
         
      Next srchart
   
   Next cht
   
Next sht

CurrentSheet.Activate
Application.EnableEvents = True

End Sub

 

[Mr. C]

Hui,
Works great! Thank you very much for the additional comments to help explain each section. Will help me to improve my own skills.
Michael

 

[Hui]

Thats the main purpose of this forum
to help people and use examples like you did