Plotting more than one series on a chart with vba

Posted by Kurt on July 02, 2001 12:57 PM

Hello everyone. I have a group of 6 check boxes and when I click on them I would like to have Excel display more than one line on a graph at a time.

Has anybody done this before?

Thanks again in advance for your help. Please help asap this is very urgent!!

Posted by Damon Ostrander on July 02, 2001 7:07 PM

Hi Kurt,

I assume that each checkbox corresponds to one series that you want to show on the chart. There are a number of ways to do this. Here are two:

1) In each checkbox's change event code, add a SeriesCollection using the Add method:

Worksheets("Sheet1").ChartObjects(1).SeriesCollection.Add _
Source:=Worksheets("Sheet1").Range("B1:B10")

This is the form that applies to a chart embedded in a worksheet. Just modify the sheet name and range to suit. There is a comparable Delete method that applies to a Series object if you want to remove a series from your chart. You can find out about these in the VBA helps (you have to be in the VBA Editor (VBE) to access these help files.

2) You can assign the checkboxes to either a column grouping or hide command to hide the series' you don't want to appear. When a column of data used in a chart series is hidden or grouped, its series disappears from the chart. For example if a particular series' data are in column 5, you can hide it with the code:

Columns(5).Hidden = True

and this will make it disappear from the chart. T make it reappear, set it to False.

To tie this to your checkbox, just set the Hidden property to the opposite of the value of the checkbox:

Columns(5).Hidden = Not CheckBox1.Value

Happy charting.

Damon