Excel : Interactive Chart Using Check Boxes

Deepanshu Bhalla 1 Comment , ,
This tutorial explains how to create interactive chart with check boxes in Excel.

Check Box

The Check Box form Control allows the selection of multiple items from a list of items. When one item is selected, the other check box can also be checked.
Interactive Chart with Check Boxes
Important Steps

1. Add Check Boxes

2. Add Group Box and group all the check boxes (Use CTRL SHIFT to select check boxes)

3. Define Name Range for cell reference of 3 check boxes combined (Except "All" box).

Name Range

4. Press ALT F11 and Go to Insert >> Module and paste the following code
Option Explicit
Sub ChartUsingCheckBox()
Dim rCell As Range
Range("O2").Formula = "FALSE"
For Each rCell In ActiveSheet.Range("CheckBoxRng2").Cells
rCell.EntireColumn.Hidden = Not rCell.Value
Next
If Range("L2").Value = True And Range("M2").Value = True And Range("N2").Value = True Then
Range("O2").Formula = "TRUE"
ElseIf Range("L2").Value = False And Range("M2").Value = False And Range("N2").Value = False Then
Range("O2").Formula = "FALSE"
End If
End Sub 
Sub ChartUsingCheckBox2()
If Sheet1.Range("O2").Value = True Then
    Range("L2:N2").Formula = "TRUE"
    Columns("L:N").EntireColumn.Hidden = False
ElseIf Sheet1.Range("O2").Value = False Then
    Range("L2:N2").Formula = "FALSE"
    Columns("L:N").EntireColumn.Hidden = True
End If
End Sub
Note : 
O2 - Cell link of "All" CheckBox
L2,M2,N2 - Cell link of the remaining 3 check boxes
5. Assign the "ChartUsingCheckBox2" macro to "All" checkbox and "ChartUsingCheckBox" to each of the remaining 3 check boxes.

Download WorkBook
Related Posts
Spread the Word!
Share
About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 1 Response to "Excel : Interactive Chart Using Check Boxes"
  1. Hello,
    Thank for this tutorial.
    I would like to hide 2013 and 2014 in the same time with only one check box. But I don't find a way to code this, do you have an idea by any chance ? Please

    ReplyDelete
Next → ← Prev