VBA Clear Chart Dynamically and refresh with new values

jammerules

New Member
Joined
Nov 16, 2009
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a chart named "VelChart". It fetches data from a set of dynamically created cell values. However, I want the chart to be cleared of any graph every time I said referenced cell values are refreshed.

1669224644720.png


I have this code that isn't working:

VBA Code:
        Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
        ActiveChart.ChartArea.ClearContents

When I use the above code, it is deleting the chart altogether. And when I try to reference the chart elsewhere in the code, it throws an error. How do I just clear the chart contents and not delete the chart altogether?


This is the code that I have elsewhere that I am referencing:

VBA Code:
Public Function DrawVelChart(strComponent As String)
    Select Case strComponent
        Case "Infrastructure (CI/CD/DevOps)"
            Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
            ActiveChart.SetSourceData Source:=Worksheets("Velocity Tracker").Range("C6:E" & iLastRow)
            ActiveChart.ChartTitle.Text = "Infra Velocity Trend"
            ActiveChart.SeriesCollection(1).XValues = Worksheets("Velocity Tracker").Range("G6:G" & iLastRow)
        Case "Configuration"
            Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
            ActiveChart.SetSourceData Source:=Worksheets("Velocity Tracker").Range("C6:E" & iLastRow)
            ActiveChart.ChartTitle.Text = "Configuration Velocity Trend"
            ActiveChart.SeriesCollection(1).XValues = Worksheets("Velocity Tracker").Range("G6:G" & iLastRow)
        Case "Analytics"
            Worksheets("Velocity Tracker").ChartObjects("VelChart").Activate
            ActiveChart.SetSourceData Source:=Worksheets("Velocity Tracker").Range("C6:E" & iLastRow)
            ActiveChart.ChartTitle.Text = "Analytics Velocity Trend"
            ActiveChart.SeriesCollection(1).XValues = Worksheets("Velocity Tracker").Range("G6:G" & iLastRow)
    End Select
End Function
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Maybe something like this...
Code:
Dim S As Series
For Each S In Sheets("Velocity Tracker").ChartObjects("VelChart").Chart.SeriesCollection
      S.Delete
Next S
HTH. Dave
 
Upvote 0
Thanks for the reply, Dave. Sadly, it didn't do anything. However, if I put a breakpoint at the beginning of the 'For' and loop thru, then it is clearing the chart. What gives?
 
Upvote 0
If you place the code in the function it should work. I suspect that you need to activate the chart before running the code by itself. Trial...
Code:
Sheets("Velocity Tracker").ChartObjects("VelChart").Activate
before running the code. HTH. Dave
 
Upvote 0
I created a function and called it from the sub-routine. Even then it wouldn't clear upon calling the function.

1669256431603.png



1669256449517.png
 
Upvote 0
Well I'm not quite sure. I use that code without problems. Maybe trial selecting the sheet, then activating the chart and then removing the series. So...
Code:
Sheets("Velocity Tracker").Select
Sheets("Velocity Tracker").ChartObjects("VelChart").Activate
For Each S In Sheets("Velocity Tracker").ChartObjects("VelChart").Chart.SeriesCollection
      S.Delete
Next S
Dave
 
Upvote 0
Well, that is puzzling. Perhaps Jon or others will have time to offer further assistance. Sorry I wasn't able to help. Dave
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top