Hi Jan,

Make sure you reference the sheet properly, instead of ActiveSheet:

Set mySeriesCol = ThisWorkbook.Worksheets("A").ChartObjects(1).Chart.SeriesCollection

Do you have more than 1 chart in that sheet?
Make sure it's the right chart:
Debug.Print ThisWorkbook.Worksheets("A").ChartObjects(1).Name
Debug.Print ThisWorkbook.Worksheets("A").ChartObjects(2).Name
Debug.Print ThisWorkbook.Worksheets("A").ChartObjects(3).Name

If you know the name of the chart, you can use it directly:
Set mySeriesCol = ThisWorkbook.Worksheets("A").ChartObjects("ChartName").Chart.SeriesCollection

Hi Catalin,

Thanks for the effort!

Unfortunately, I can't get the trendline back with the script above, using your modification, after I've hit on a graph with no data and no graph curve.

The next graph is presented with a curve, but no trendline. 

The VBA script seems to work. There is no bug report when I change the page name in the VBA script to "Enskild" and the chart name reference to 

"Chart1"Set mySeriesCol = ThisWorkbook.Worksheets("Enskild").ChartObjects("Chart1").Chart.SeriesCollection

Question: do I place the VBA "trendline" call in the right place? See attached image. I can only see a "page VBA module", no "chart VBA module".

All the best and thanks again,

Regards,
Jan

There is no image, make sure you press the Start upload button after you press the button to Select files.

Hi Catalin, 

Here is the picture.

Regards, 

Jan 

Hard to debug without a sample file, lot of wasted time to understand what you're doing there.

You are using an event for a pivot table update to call the code that adds the trendline.

Do you have one or more than one pivot tables in that sheet?

Hi Catalin, 

Thanks for making the effort

I spent an hour trying to re-create the problem with phony data. The trendline came back even when I didn't have any VB-script, but some numeric data  "Y=5656..." was added to the line.

To answer your question: there is only one pivot table on this sheet.

However, I found the error by accident when checking the sheet once again. A duplicate slicer hid below the presentation! I found it when I scrolled down.  When I removed the duplicate the trendline didn't disappear after coming up on a slicer setting that produced no data or line. BUT instead, the numeric data was added from my re-creation effort. I assume it shows the angle of the line. 

I don't expect you to help me solve this added issue, I just wrote this to show you that I spent some time trying to figure out the trend line issue based on your suggestion. 

All the best, 

Jan 

I know it takes an effort to build a sample file, but it's the fastest way to solve a problem, otherwise there will be lot of messages to clarify things.

How many series you have in that chart? (mySeriesCol.Count=?) The trend line must be set for which series from this collection, if there are more than 1 items in this collection?

Hi, 

It's one series in this chart. It's from the cost column. 

Now when I review the dashboard, I see that the VB script doesn't run automatically when I open the file. When I do run the macro, by manually starting it from the VBA project "Run Macro" window, the trend line appears. Later, when I use slicer setting that doesn't contain data and doesn't produce a line chart, the trend line doesn't re-appear in the next step, when I choose something else that has data and produces a line on the chart. 

It seems the script doesn't kick in and checks the status of the trend line after each selection. Sure hard to figure out.

Regards, 

Jan 

Use the red line in your code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
MsgBox  Target.Name & ", " & Target.Databodyrange.address
End Sub

When the pivot is updated, you should see that message. Of course, the slicer must be connected to the pivot.

That code will run on file open only if you call that code from Workbook_Open event, 
which you did not mentioned before and there is no file where we can check this, of course.

Hi Catalin, 

Thanks for your perseverance.

Yes, the slicers are connected to the chart. I've done multiple dashboards and finished excellent courses provided by Mynda/MyOnHub. It's VB-scripting that is a black hole of non-existing knowledge for me.
I get an error when I remove the VB-script from the sheet "Enskilda" and instead place it into"ThisWorkbook". Question: can I combine a call to Workbook and Worksheet? You didn't instruct me to remove the call to the Worksheet in the code.

This is how the code looks right now..

In module 1: 

Sub AddTrendLine()
Dim mySeriesCol As SeriesCollection
Set mySeriesCol = ThisWorkbook.Worksheets("Enskild").ChartObjects("Chart1").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

In "ThisWorkbook"  

Private Sub Workbook_Open()

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine
MsgBox Target.Name & ", " & Target.DataBodyRange.Address
End Sub
End Sub

/All the best - Jan 

To call the code at workbook open:

Private Sub Workbook_Open()
Call AddTrendLine

End Sub

To call the code from sheet module, using an event that triggers the code:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Call AddTrendLine

End Sub

(the additional message box line is just for testing, you can add it in both places)

Here we are at message 12, in 6 days, for a problem that could be solved in 10 minutes with a sample file.

Hi Catlin, 

Thank you for your replies and effort!  I will not harass you further on this issue.

Have a nice weekend! 

Regards, 

Jan 

No worries, you can add as many messages you want, if the issue is not solved