How do I made data series visible or invisble in VBA

[Raymond Lillard]

I am developing an Excel 2007 solution to collect data from a specific
set of instruments. I have arranged the workbook with 11 sheets. The
first sheet is a summary sheet and the remaining 10 are used to collect
data during a single instrumentation run.

I am plotting data from each process run on a chart embedded in the
worksheet where the data was collected. I am plotting all 10 runs on the
summary sheet (sheet1). All of this works fine.

I would like to add 10 checkboxes on the summary sheet to enable the
researcher hide or show each of the 10 plots from the other individual
runs on the summary chart which is also embedded in the summary sheet
(sheet1).

I don't want to actually remove any series from the chart object, just
make a series invisible or visible.

I suspect what I want to do is not possible. If true, I would like
suggestions to achieve a similar result.

I know how to "Add" a data series to the SeriesCollection, but there
seems to be no companion "Delete" or "Remove" method.

Thank you for your time.
Ray

 

[Andy Pope]

Hi,

There is a Delete method for a seriescollection item.

Activechart.seriescollection(1).delete

But deleting the series will remove it from the chart/legend. Of course you
can use the .Add method to put it back but you will also need to use the
PlotOrder value to keep series in original order

Another option, non VBA, is to use formula to display the use of value or
NA(). If all points are #N/A then the series line will not be plotted
although the legend entry will remain.
http://peltiertech.com/Excel/Charts/ChartByControl.html

Or you could hide the column/row of data used by the series. This will
remove it from both chart and legend but unhiding the cells will replace the
series in the correct order. To use this approach make sure the Plot Visible
cells only property is set. (Tools/Options/Chart)

Cheers
Andy