By definition an interactive dashboard contains methods for users to control which information is displayed. This can be accomplished in many ways. In Interactive Charting by Combo Box or Checkbox I wrote about using checkboxes and worksheet formulas to do this, and I elaborated on the technique in Interactive Charts with Checkboxes and Formulas.
There are a couple of shortcomings with the checkboxes-plus-formulas approach. If a series is not displayed, there is still a space for its bars in a column chart. Also, the legend key (marker in the legend) for the series is still present even if the label is hidden.
By using VBA and checkboxes together, these shortcomings can be avoided. This technique makes use of Excel’s default behavior, which does not show data in a chart if the data range is hidden. This behavior can be changed on a chart by chart basis, so that changing it for one chart does not change it for other charts.
Checkboxes and VBA
Start with a line or column chart that includes all of the series you will allow the user to select from (A, B, and C in this example). Add a checkbox from the Forms toolbar for each series.
The data is very simple, as shown below. The source data for the chart is in range J3:M8, and K1:M1 are the cells that the checkboxes are linked to. The range K1:M1 is named “TrueFalseRange” (select the range, click in the Name Box and type TrueFalseRange, and press ENTER).
In addition to the checkboxes being linked to cells in the TrueFalseRange, each is also assigned to a macro named ClickSeriesTextbox. The macro is very simple:
Sub ClickSeriesCheckbox() Dim rCell As Range For Each rCell In ActiveSheet.Range("TrueFalseRange").Cells rCell.EntireColumn.Hidden = Not rCell.Value Next End Sub
All this macro does is check each cell in TrueFalseRange, if the value is False it hides the whole column, otherwise the column is displayed.
When (for example) the checkbox for Series B is unchecked, column L is hidden (signified by the thicker line between the column headers for columns K and M).
Series B is completely eradicated from the charts, leaving behind no spurious legend key, and no empty slot for Series B’s columns in the column chart.
If the user unchecks all of the checkboxes, all of the series value columns are hidden (note the darker line between the headers for columns J and N).
Not only are all series removed from the charts, but so are the axes, titles, legends, and other chart features. The chart is only visible in this screen shot because it has been activated.
CheckboxAndVBA.zip is a zipped workbook that illustrates this technique.