Log In
Register
Home
August 23, 2019
Offline
Hi all,
The trendline in a chart disappears when I select something with the slicer that doesn't contain any data. I found a VBA script on chandoo.org that addresses the issue, but I can't make it work. I followed the instructions and put the first part in a new module named "module1" and the other on the worksheet VBA page/module. Is the code below correct? What am I missing? I only need to call the "AddTrendline" function in the VBA script below for one chart named "A1" on one worksheet named "A".
Any help on this issue is highly appreciated! Your name will fly among the stars if you can solve this issue for me and other Excel trend line followers. 
Yours Truly,
Jan, Sweden
https://chandoo.org/forum/thre.....ick.29545/
----------------------------------------
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.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
November 8, 2013
Offline
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
August 23, 2019
Offline
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
November 8, 2013
Offline
There is no image, make sure you press the Start upload button after you press the button to Select files.
August 23, 2019
Offline
Hi Catalin,
Here is the picture. 
Regards,
Jan
November 8, 2013
Offline
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?
August 23, 2019
Offline
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
November 8, 2013
Offline
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?
August 23, 2019
Offline
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
November 8, 2013
Offline
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.
August 23, 2019
Offline
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
November 8, 2013
Offline
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.
1 Guest(s)
