Macro to change what's displayed in the graph

[Stephenf_07]

Hello,

I currently have a line graph that display 9 different categories in the legend over a period of 3 weeks. With 9 different lines going on at once in the graph, it can be tricky to view as the graph is not very big. I have inserted 9 different checkbox forms next to the graph with my categories next to it. Is there a way that I can assign a Macro to the checkbox that basically states when the box is checked, the line is displayed in the graph and when the box is not checked, the data is not displayed? This way someone who is not user friendly using the graph could easily just check the box's of only the categories they would like to display on the graph.

I am using Excel 2010. The line graph is just a standard 2D graph.

Legend:
Category 1
Category 2
Category 3
Category 4
Category 5
Category 6
Category 7
Category 8
Category 9

Y Axis:
1
2
3
4
5


X Axis:
Week 1
Week 2
Week 3

EX.) Checking the checkbox next to Category 5 will only display 1 line on the graph.

Hope this makes sense! Thanks in advance for any help!

 

[Derek Brown]

I make use of the feature where Excel charts ignore cells with #N/A.
I would link each check-box to a cell and this would then contain either TRUE or FALSE.
Each of those cells would be given a 'name' - e.g. Required1, Required2 etc.
Then the formula in each cell that feeds the chart needs to be changed so, for example:
='Sheet1'!A1
would become:
=IF(Required1,'Sheet1'!A1,NA())
This will not affect the legend and, if that is a problem, you can partly deal with it using conditional formatting for the font (making it 'white'). I don't think that it can be completely removed because the series is still associated with the chart - it is just that the values will not be plotted.

 

[Stephenf_07]

Great idea! This worked perfectly! Thanks so much!

 

[Rasm]

If you want to zoom the chart and unzoom it - here is a cool piece of code - all you have to do is click the chart and it zooms to fit the screen - click it again and it unzooms

http://www.excelfox.com/forum/showthread.php?2-Chart-Zoomer-(Resizer)

 

[Derek Brown]

In case anyone stops by this post and is interested in the zooming chart mentioned above, also look at Stephen Bullen's "FunChrt7.zip" download of an example of how to both scroll and zoom a chart:
http://www.oaltd.co.uk/Excel/Default.htm