Trendlines Required to Remain on Charts after Slicer Changes

[Mike808]

SO, earlier in the week, I was getting help in this forum with a problem I had on my trendlines for my dashboard. Hui figured it out for the most part, with one last issue:

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.

A link to my dashboard is:
https://www.dropbox.com/s/c51adklb9ywe6p6/Master Dashboard_09132016.xlsm?dl=0

I created a module and entered this code:

Sub AddTrendLine()Dim mySeriesCol As SeriesCollectionDim i AsLong, j AsInteger

ThisWorkbook.Sheets(7).Activate
If ActiveSheet.ChartObjects.Count > 0 Then
  For i = 1 To ActiveSheet.ChartObjects.Count
  ActiveSheet.ChartObjects(i).Select
   
  OnErrorResumeNext
  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
EndIf
Application.CutCopyMode = False
EndSub

Then on Sheet 7 (my charts sheet) I entered this code:

PrivateSub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)Call AddTrendLineEndSub

Thanks for any help anyone could provide!! I have been hitting my head since Wednesday!

Mike808

 

[Chihiro]

While code and slicer applies to Sheet7. The trigger is set to PivotTable update event, which reside in Sheet8. So the code for PivotTable_Update needs to be in Sheet8 module.

Edit: Oh, and you don't need to activate Sheet7 as slicers are there. I'd change code to something like...

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection
Dim ws As Worksheet
Dim i As Integer
Dim mychart As ChartObject


Set ws = Worksheets(7) 'Or just Activesheet

For Each mychart In ws.ChartObjects
    Set mySeriesCol = mychart.Chart.SeriesCollection
    For i = 1 To mySeriesCol.Count
        If mySeriesCol(i).Trendlines.Count > 0 Then
        Else
            mySeriesCol(i).Trendlines.Add
        End If
    Next
Next mychart

End Sub

 

[Mike808]

Chihiro-

Genius my friend!! My last dashboard was a one sheet product, with the tables and charts on the same sheet!!! Many thanks!! VBA is a continuous uphill climb for me.....this will help me a ton in the future as I continue to study.

Mike808

 

[Mike808]

While code and slicer applies to Sheet7. The trigger is set to PivotTable update event, which reside in Sheet8. So the code for PivotTable_Update needs to be in Sheet8 module.

Edit: Oh, and you don't need to activate Sheet7 as slicers are there. I'd change code to something like...

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection
Dim ws As Worksheet
Dim i As Integer
Dim mychart As ChartObject


Set ws = Worksheets(7) 'Or just Activesheet

For Each mychart In ws.ChartObjects
    Set mySeriesCol = mychart.Chart.SeriesCollection
    For i = 1 To mySeriesCol.Count
        If mySeriesCol(i).Trendlines.Count > 0 Then
        Else
            mySeriesCol(i).Trendlines.Add
        End If
    Next
Next mychart

End Sub

ChiHiro-
Is there easy code to add to rename in the legend each of these trendlines that this code adds?? Current names are the default (Linear FRC E for example) and I would like to change them to something like this: FRC E Trendline. It seems like it would be hard code because it would essentially have to rename each time the slicer changes.

Mike808