How to create an interactive Excel chart [VBA]
Author: Oscar Cronquist Article last updated on September 03, 2019
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
Built-in Charts
Combo Charts
Combined stacked area and a clustered column chartCombined chart – Column and Line on secondary axis
Combined Column and Line chart
Chart elements
Chart basics
How to create a dynamic chartRearrange data source in order to create a dynamic chart
Use slicers to quickly filter chart data
Four ways to resize a chart
How to align chart with cell grid
Group chart categories
Excel charts tips and tricks
Custom charts
How to build an arrow chartAdvanced Excel Chart Techniques
How to graph an equation
Build a comparison table/chart
Heat map yearly calendar
Advanced Gantt Chart Template
Sparklines
Win/Loss Column LineHighlight chart elements
Highlight a column in a stacked column chart no vbaHighlight a group of chart bars
Highlight a data series in a line chart
Highlight a data series in a chart
Highlight a bar in a chart
Interactive charts
How to filter chart dataHover with mouse cursor to change stock in a candlestick chart
How to build an interactive map in Excel
Highlight group of values in an x y scatter chart programmatically
Use drop down lists and named ranges to filter chart values
How to use mouse hover on a worksheet [VBA]
How to create an interactive Excel chart [VBA]
Change chart series by clicking on data [VBA]
Change chart data range using a Drop Down List [VBA]
How to create a dynamic chart
Animate
Line chart Excel Bar Chart Excel chartAdvanced charts
Custom data labels in a chartHow to improve your Excel Chart
Label line chart series
How to position month and year between chart tick marks
How to add horizontal line to chart
Add pictures to a chart axis
How to color chart bars based on their values
Excel chart problem: Hard to read series values
Build a stock chart with two series
Change chart axis range programmatically
Change column/bar color in charts
Hide specific columns programmatically
Dynamic stock chart
How to replace columns with pictures in a column chart
Color chart columns based on cell color
Heat map using pictures
Dynamic Gantt charts
Stock charts
Build a stock chart with two seriesDynamic stock chart
Change chart axis range programmatically
How to create a stock chart
Excel categories
19 Responses to “How to create an interactive Excel chart [VBA]”
Leave a Reply
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.
Hi Oscar, excellent site and very helpful and easy to follow solutions and tips. I've been a long time vistor to this site and you have solved many problems for me, thanks.
I am trying to use the above code, but I get this error... -2147352571 (80020005) Type mismatch
On this line...
Any suggestions on what may be causing this error?
My "Table" is in the range..U4:X7
My Chart is on a seperate sheet (Dashboard) to the Table
Thanks
Ak
Ak,
I am using rounded rectangles as buttons.
Insert | Shapes | Rectangles
If you press with left mouse button on a shape the following code changes it´s color:
With ActiveSheet.Shapes(Application.Caller).Fill.ForeColor If .Brightness = 0 Then .Brightness = -0.150000006 Else .Brightness = 0 End If End WithMaybe you are not using shapes?
Hi Oscar, thanks for the reply.
I am indeed using shapes, as directed in your instructions above, Rounded Rectangles.
I have just opened the workbook and tried again and I am now getting this error...
Run-time error '438':
Object doesn't support this property or method
on this line...
MS Help offers me this...
"Object doesn't support this property or method (Error 438)
Not all objects support all properties and methods. This error has the following cause and solution:
You specified a method or property that doesn't exist for this Automation object.
See the object's documentation for more information on the object and check the spellings of properties and methods.
You specified a Friend procedure to be called late bound.
The name of a Friend procedure must be known at compile time. It can't appear in a late-bound call."
But that doesn't tell me anything!!
Sorry to be an idiot about this.
Ak
Ak,
Can you upload an example file?
The rounded rectangles were named Rounded Rectangle 7, 10 and 11 on AK´s sheet.
Change this line:
temp = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3")
to
temp = Array("Rounded Rectangle 7", "Rounded Rectangle 10", "Rounded Rectangle 11")
I'm getting the following error:
Run-time error ‘-2147024809 (80070057)’
The item with specified name wasn’t found.
With ActiveSheet.Shapes(temp(i))
What could be wrong? How do I find the name of my rounded rectangle shapes? Maybe that's the problem...
Thank you!
Rodrigo Canar,
How do I find the name of my rounded rectangle shapes?
1. Select a shape (Press with right mouse button on the shape)
2. Read the name in the name box
Thank you. That was easy and the macro now is running without giving me any error messages, but it is not working properly. Every time I press with left mouse button over one of the three buttons, the displayed graph is not accurate. If I repeat a press on the same button, the result changes every time... Any idea?
Hi,
Even the attached example is also showing the same error. Can you please fix this.
Pankaj,
What error?
Run time error
Object doesn't support this property or method on debug get following method.
If .Brightness = 0 Then
This happen with:
Get the Excel *.xlsm file
Chart.xlsm
Pankaj,
The file works here (excel 2010), your excel version?
Hello Oscar,
I am having excel 2007, is it possible with excel 2007.
Regards,
Pankaj
Hello,
Waiting for your kind reply can this work for 2007.
Regards,
Pankaj
Pankaj,
I don´t get the error. I am not sure what is wrong.
Hello,
This page has been extremely helpful but I'm an receiving the below error message when attempting to run the macro. Can anyone help?
Field:=1, Criteria1:=Series, Operator:=xlFilterValues
Compile Error: syntax Error
Thank you
CasesST,
Have you made any changes to the sheet?
three buttons works perfectly fine,
i need about 6 button, how can i add more button to this chart?
Any assistance you can offer would be greatly appreciated.
Anant,
Make sure your buttons have the same button text values as the values in the Excel defined table column1, also name the buttons:
Rounded Rectangle 1
Rounded Rectangle 2
Rounded Rectangle 3
Rounded Rectangle 4
Rounded Rectangle 5
Rounded Rectangle 6
Change this line:
temp = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3")to
temp = Array("Rounded Rectangle 1", "Rounded Rectangle 2", "Rounded Rectangle 3", "Rounded Rectangle 4", "Rounded Rectangle 5", "Rounded Rectangle 6")