Adding and removing series to a chart

Hi Everyone,

I have a problem that should be simple to solve, but its giving me a headache.

I want to use 4 toggle buttons to be able to add and remove 4 different series to a chart. When the toggle button is clicked, a specific series is added to a chart, and when it is unclicked then the series would be removed.

The problem I am running into is that unless I add and remove each series in a specific order, I will get an error because I may be trying to remove a series whose series number is not on the series collection in the chart.

for example: if I remove series number 2 with the toggle button for that specific series, then the toggle button for series number 3 will remove the old series 4 (which is now 3), and the toggle button for series 4 won't work because series 4 will now have become series 3.

Therefore, would there be a way to remove a series based on the name of the series, rather than the series collection number?

Any help is much appreciated.

Thanks!

Try to adapt the following...

Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection("SeriesName").Delete

With Worksheets("Sheet1").ChartObjects("Chart 1").Chart.SeriesCollection.NewSeries
    .XValues = Worksheets("Sheet1").Range("A2:A5").Value
    .Values = Worksheets("Sheet1").Range("B2:B5").Value
    .Name = "SeriesName"
    .PlotOrder = 1
End With

Hope this helps!

Hi Andy,

I've done something similar to what you're suggesting by hiding and showing columns, in another project, but the problem I ran into is that I had too much data to display on the screen (I had 6 charts, and over 40 columns with with several 1000 rows of data), and excel would become really slowly because it had to hide and show much data.

Domenic's answer up above almost works perfectly, except for the plot order. if remove series 3 and 4, and later want to only bring back series 4, then there will be an error. is there a way to code so that if there is an error in the plot order, then make it the last series in the plot?

thanks.

Try something like this...

Dim MyChart         As Chart
Dim MySeries        As Series
Dim MyPlotOrder     As Integer

Set MyChart = Worksheets("Sheet1").ChartObjects("Chart 1").Chart

MyPlotOrder = 4
If MyPlotOrder > MyChart.SeriesCollection.Count + 1 Then
    MyPlotOrder = MyChart.SeriesCollection.Count + 1
End If

Set MySeries = MyChart.SeriesCollection.NewSeries
With MySeries
    .XValues = Worksheets("Sheet1").Range("A2:A5")
    .Values = Worksheets("Sheet1").Range("E2:E5")
    .Name = Worksheets("Sheet1").Range("E1")
    .PlotOrder = MyPlotOrder
End With

Hope this helps!