Chart that updates with check boxes

[Libertineash]

Hi,

Can anyone help me with creating a bar chart that will update with the info it is showing when i tick or untick a check box.

So far I have my data list with check boxes and my bar chart however it shows all the data, I only want it to show the ones that are ticked, but for it to update automatically when some one else ticks or unticks different rows.

Is there a way to link the bar chart to the check boxes?

 

[Hamburgler]

I saw an example of this once where the check boxes hide and unhide the chart source data columns. When one of the columns for the source data is hidden the chart should update to exclude that series.

 

[Jon von der Heyden]

Hi

What version of excel are you using?

You can have it that your chart 'plot visible cells only', so one way is to hide rows/columns and have it entirely excluded.

Of course you would have difficulty doing this with check boxes, but perhaps you can just tell the users to do this.

To set this first select your chart
Excel 03:
Tools > Options > Chart (tab) and check Plot visible cells only.

Excel 07 (not entirely sure but think):
Click the offfice icon
It is in the Excel Options

 

[Libertineash]

Any Idea how I make the check boxes do that?

At the minute my check boxes create a True or False in Column F depending on whether they are ticked. I have then used a formula to say if its false (unticked) don't show me the data that is in column G, in Column H.

I have then used column H to plot my data, however the graph still plots the blank data. I have used Tools, options, chart and selected don't plot zero's but it still is...??

 

[Jon von der Heyden]

How are the chart values collected in the range? I.e. are they formula?

You could replace the blanks with #N/A's instead, which also are not plotted.

E.g in H2:
=IF(F2,G2,NA())

 

[Libertineash]

Jon I'm using 2003.

I have tried the plot visible cells only. I think because my data column uses a forumla depending on whether the check box is ticked or not excel doesn't recognise the cell as blank...I think!?
The formula I have in Column H is =IF(F7= TRUE,G7, NA())
This is still being plotted by the bar chart as a zero.

I also tried using advanced filter on a hidden sheet that took the original data set and filtered for non blank cells.
This works for the first version of the bar chart however it doesn't update automatically when a check box is then unticked or ticked. I need to manually refresh the autofilter.

 

[Hamburgler]

I saw the example of this in John Walkenbach's book / CD. There may be a walkthrough / example file of this on his website or try searching here more.

I think that it will require VBA as I think you need to either hide the columns or change the chart source data range dynamically.

To hide the columns you need to link the (I used ActiveX) checkboxes to a macro that would look something like this:

Private Sub CheckBox1_Click()
  Columns(5).EntireColumn.Hidden = Not CheckBox1.Value
End Sub