Using Check Box to turn on/off lines in a graph

[Pigmy]

I'm developing a performance dashboard where I show the weekly output from a range of machines. I want to let the user decide if they want to overlay the graph with the machine shop average using a check box. So, I have the formula for machine_1 and the shop_average as 2 separate defined names. In normal circumstances, the graph will just plot the one line, for machine_1. When they check the box, the graph overlays the shop_average.

I've set the check box up and using the True/False that comes from that I can change the values of the shop_average name, however it will always show up on the graph. My current thinking is to set these values to 0 so the line coincides with the axis but this is really clunky. I'd really like to turn the line on and off using the check box. Any ideas?

Thanks

 

[Teeroy]

You can hide the row / column with the data in it which prevents the line for that series being shown on the chart.

 

[Pigmy]

Thanks Teeroy - you're a genius. Such a simple solution. I'm going to use a little module to do this - or am I over thinking things again?

 

[Teeroy]

If you are using ActiveX CheckBox Controls then you can use the events to do this.

In the sheet code module (that the CheckBox is on) use something like:

Private Sub CheckBox1_Change()
If CheckBox1.Value = True Then
    Rows(2).Hidden = True
Else
    Rows(2).Hidden = False
    End If
End Sub

 

[Jon Peltier]

Rather than using code, use formulas.

For example, let the machine_1 data be in B2:B10, and the shop_average data in C2:C10.

Use cell D12 as the checkbox linked cell.

In D2 enter this formula and fill it into D3:D10:

=IF($D12,C2,NA())

Use the data in B2:B10 and D2:D10 for your chart. If the checkbox is checked, the values in D2:D10 will be plotted; if the checkbox is unchecked, the #n/a values in D2:D10 will not be plotted.

The legend will still have an entry for shop_average. But if you label the series and delete the legend, this will not matter. Apply a label to the last point of each series using the Series option.

 

[Teeroy]

@Jon Peltier, although hiding via an event does give you a slight overhead for the code execution I tend to prefer it as it hides the label as well.

 

[Jon Peltier]

If a point has a data label, and that point is not plotted because of a #N/A value, the data label is not shown.

In general, solutions that don't rely on VBA are more reliable.