• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA script to keep trend line for disappearing |VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA script to keep trend line for disappearing |VBA & Macros|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosVBA script to keep trend line for d…
sp_PrintTopic sp_TopicIcon
VBA script to keep trend line for disappearing
Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
1
August 19, 2021 - 10:39 pm
sp_Permalink sp_Print

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. Smile 

Yours Truly, 

Jan, Sweden 

 

https://chandoo.org/forum/thre.....ick.29545/

----------------------------------------

It would be something like below in regular Module.

Code:

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

Code:

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.

------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
August 20, 2021 - 5:57 pm
sp_Permalink sp_Print

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
Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
3
August 23, 2021 - 11:57 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
August 24, 2021 - 12:16 am
sp_Permalink sp_Print

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

Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
5
August 24, 2021 - 6:58 pm
sp_Permalink sp_Print

Hi Catalin, 

Here is the picture. chart_or-page_Module.pngImage Enlarger

 

Regards, 

Jan 

sp_PlupAttachments Attachments
  • sp_PlupImage chart_or-page_Module.png (185 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
6
August 24, 2021 - 7:53 pm
sp_Permalink sp_Print

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?

Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
7
August 25, 2021 - 12:36 am
sp_Permalink sp_Print

Hi Catalin, 

Thanks for making the effort Smile

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 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
8
August 25, 2021 - 12:49 am
sp_Permalink sp_Print

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?

Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
9
August 25, 2021 - 5:47 pm
sp_Permalink sp_Print

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.Confused

Regards, 

Jan 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
10
August 25, 2021 - 7:11 pm
sp_Permalink sp_Print

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.

Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
11
August 25, 2021 - 10:39 pm
sp_Permalink sp_Print

Hi Catalin, 

Thanks for your perseverance. Smile

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. Laugh
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 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
12
August 26, 2021 - 12:42 am
sp_Permalink sp_Print

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.

Avatar
Jan blomberg
Member
Members
Level 0
Forum Posts: 22
Member Since:
August 23, 2019
sp_UserOfflineSmall Offline
13
August 27, 2021 - 6:04 pm
sp_Permalink sp_Print

Hi Catlin, 

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

Have a nice weekend! 

 

Regards, 

Jan 

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
14
August 28, 2021 - 1:26 am
sp_Permalink sp_Print

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

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Misael Gutierrez Sr.
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.