How to create an interactive Excel chart [VBA]

This article describes how to create an interactive chart, the user may press with left mouse button on a button or multiple buttons and the chart shows corresponding data based on the selected buttons.

Slicers were introduced in Excel 2010, see image above. I recommend you use slicers instead if you own Excel 2010 or a later version

No VBA code is required if you choose to use slicers. Slicers let you filter values in the Excel defined Table, the chart is instantly refreshed based on your selection.

Anyway, this article may still be interesting, you will in this article learn to

Insert buttons to a worksheet.
Create a macro in a code module.
Link buttons to macro.
Filter an Excel defined Table programmatically based on selected buttons.
Highlight selected button programmatically.
Remove highlight from button if press with left mouse button oned on a second time.
Save button text to an array variable.

The animated image below demonstrates the functionality of these buttons.

Create a table

Select a cell in your data set.
Go to tab "Insert" on the ribbon.
Press with left mouse button on "Table" button.
Press with left mouse button on OK.

Create chart

Select any cell in your Excel defined Table.
Go to tab "Insert".
Press with left mouse button on "Columns" button.
Press with left mouse button on "2D Clustered column".
Press with right mouse button on on chart.
Select "Select data".
Press with left mouse button on "Switch row/column" button.
Press with left mouse button on OK.

Insert shapes

Go to tab "Insert".
Press with left mouse button on "Shapes" button.
Press with left mouse button on "Rounded Rectangle".
Create three rectangles below the chart.
Press with right mouse button on on rectangle and select "Edit text".
Type button names and make sure they match the table data in the first column (Revenue, Earnings and Employees).

VBA Code


'Name macro
Sub Chart()

'Dimension variables and declare data types
Dim temp As Variant
Dim Series() As String
Dim i As Single

'Redimension variable Series in order to make the array variable grow dynamically
ReDim Series(0)

'Don't show changes to user
Application.ScreenUpdating = False

'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
'Application.Caller returns the object that started the macro
With ActiveSheet.Shapes(Application.Caller).Fill.ForeColor

    'Check if brightness property is 0 (zero) meaning check if button is deselected
    If .Brightness = 0 Then

        'Change brightness
        .Brightness = -0.150000006

    'If brightness property is NOT 0 (zero) meaning check if button is already selected
    Else

        'Change brightness to 0 (zero)
        .Brightness = 0
    End If
End With

'Save values to array variable temp, these names correspond to the button names
temp = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3")

'Iterate through values in array variable temp
For i = LBound(temp) To UBound(temp)

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With ActiveSheet.Shapes(temp(i))
 
        'Check if brightness is -0.150000006
        If .Fill.ForeColor.Brightness = -0.150000006 Then

            'Save text in button to last container of array variable Series
            Series(UBound(Series)) = .TextFrame2.TextRange.Characters.Text

            'Add another container to array variable Series
            ReDim Preserve Series(UBound(Series) + 1)
        End If
    End With
Next i

'Check if Series variable has more than 1 container, if so remove the last container
If UBound(Series) > 0 Then ReDim Preserve Series(UBound(Series) - 1)

'Enable Autofilter for first column in Excel defined Table Table1 
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=1

'Apply filter based on values in variable Series to 
Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter _
    Field:=1, Criteria1:=Series, Operator:=xlFilterValues

'Show changes to user
Application.ScreenUpdating = True
End Sub

Where to put the VBA code?

Copy VBA code above.
Press Alt + F11 to open the Visual Basic Editor.
Select your workbook in the Project explorer.
Press with left mouse button on "Insert" on the menu.
Press with left mouse button on "Module".
Paste to code window.

Assign macro

Press with right mouse button on on one of the buttons to open a menu.
Press with mouse on "Assign macro".
Select the "chart" macro.
Press with left mouse button on OK button.

Repeat above steps with the remaining rectangles.

Recommended article:

Interactive Sales Chart using MS Excel

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form