• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Trendlines Issue

Mike808

New Member
With help from Hui last night, I was able to figure out how to add trendlines to my chart page in my workbook. I have the identical (at least I thought) chart page in another spreadsheet and wanted to add the VBA to the xlsm. Now I am getting a runtime error in the "mySeriesCol(j).Trendlines.Add line. Need help. Seriously have no idea why I am getting the runtime error, same exact charts and data, just on a different workbook.

Code:
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
  
  
  
  End If
  Next j
  
  Next i
  End If
Next sh

End Sub
 
That code is working fine

Is the Worksheet where it is stopping a Chartsheet?

can you post the file ?
 
Mike

There are a Number of Chart Types in which Trendlines are not appropriate or available ie: Pie Charts, Stacked Column Charts

So I have added some code to get around that:

Code:
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
  sh.Select
  'Debug.Print i, sh.Name, sh.ChartObjects(i).Chart.Name
  sh.ChartObjects(i).Select
  
  If sh.ChartObjects(i).Chart.ChartType = 52 _
  Or sh.ChartObjects(i).Chart.ChartType = 53 _
  Or sh.ChartObjects(i).Chart.ChartType = 5 _
  Or sh.ChartObjects(i).Chart.ChartType = -4111 Then
  Else
  Set mySeriesCol = sh.ChartObjects(i).Chart.SeriesCollection
  For j = 1 To mySeriesCol.Count
  If mySeriesCol(j).Trendlines.Count = 0 Then mySeriesCol(j).Trendlines.Add
  Next j
  End If
  Next i
  End If
Next sh

End Sub

You can see a full list of Chart Type numbers here:
https://msdn.microsoft.com/en-us/library/office/ff838409.aspx


But a better method might just be to allow for the error and skip past it

Code:
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
  sh.Select
  'Debug.Print i, sh.Name, sh.ChartObjects(i).Chart.Name
  sh.ChartObjects(i).Select
  
  On Error Resume Next 'This line says goto the next line if there is an error
  Set mySeriesCol = sh.ChartObjects(i).Chart.SeriesCollection
  For j = 1 To mySeriesCol.Count
  If mySeriesCol(j).Trendlines.Count = 0 Then mySeriesCol(j).Trendlines.Add
  Next j
  Next i
  End If
Next sh

End Sub
 
Hui-
The original code you made yesterday (at the top of the thread) enabled the trendlines and left them on no matter what slicer was enabled or disabled. It was perfect! Now, it does not, and I only need the trendlines on Sheet 7 (FRC Dashboard). This is driving me nuts.
 
Hui-
Not exactly. It adds trendlines to all sheets that have charts in the workbook. And it doesn't stay active when I change the filters on the FRC Dashboard. I just need them on Sheet 7 and always active once the macro is ran. The original code ran perfectly, however, I know that it was because it was the only chart sheet in the workbook. This dashboard has a ton more charts and more sheets.
 
Try the following code

I would Assign it to the Slicer Group on Worksheet 7

Code:
Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection
Dim i As Long, j As Integer

ThisWorkbook.Sheets(7).Activate

If ActiveSheet.ChartObjects.Count > 0 Then
  For i = 1 To ActiveSheet.ChartObjects.Count
  ActiveSheet.ChartObjects(i).Select
   
  On Error Resume Next
  Set mySeriesCol = ActiveSheet.ChartObjects(i).Chart.SeriesCollection
  For j = 1 To mySeriesCol.Count
  If mySeriesCol(j).Trendlines.Count = 0 Then mySeriesCol(j).Trendlines.Add
  Next j
  Next i
End If
Application.CutCopyMode = False

End Sub
 
Hui-

You are a genius!! Excel Ninja is right! So close.....once I change the slicer filters (select or deselect them) the trendlines go away. When the macro runs, it initially is perfect.

So, I am not misleading myself:

I put this in Module 1:
Code:
Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection
Dim i As Long, j As Integer

ThisWorkbook.Sheets(7).Activate

If ActiveSheet.ChartObjects.Count > 0 Then
  For i = 1 To ActiveSheet.ChartObjects.Count
  ActiveSheet.ChartObjects(i).Select
   
  On Error Resume Next
  Set mySeriesCol = ActiveSheet.ChartObjects(i).Chart.SeriesCollection
  For j = 1 To mySeriesCol.Count
  If mySeriesCol(j).Trendlines.Count = 0 Then mySeriesCol(j).Trendlines.Add
  Next j
  Next i
End If
Application.CutCopyMode = False

End Sub

Then I put this on Sheet 7:

Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
End Sub
 
Great news
Of course I messed up replying.....
I need the trendlines to remain no matter what I do with the slicers. Right now the VBA code turns the trendlines off when I change the slicer filters.....this I don't want. Once I run the macro, I want the trendlines to remain on the charts until I manually shut them off via the chart elements checkbox.
 
Back
Top