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:
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article demonstrates macros that automatically changes the chart bar colors based on the corresponding cell, the first example is […]
The calendar shown in the image above highlights events based on frequency. It is made only with a few conditional […]
Fatou asks: Going back to my question, I had created a table and used the data to create a chart. […]
You can easily change data labels in a chart. Select a single data label and enter a reference to a […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
Today I am going to show you how to create a dynamic Gantt chart in excel 2007. A Gantt chart helps […]
The picture above shows a chart that has custom data labels, they are linked to specific cell values. This means […]
I will in this article demonstrate how to set up two drop down lists linked to an Excel chart, the […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to insert pictures to a chart axis, the picture above shows a column chart with country […]
I made a heat map calendar a few months ago and it inspired me to write this article. The heat […]
This article demonstrates a macro that changes y-axis range programmatically, this can be useful if you are working with stock […]
This article demonstrates how to highlight a bar in a chart, it allows you to quickly bring attention to a […]
This article describes a macro that hides specific columns automatically based on values in two given cells. I am also […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to highlight a line in a chart based on the selected item in a drop-down list. […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that […]
This article describes how to create a map in Excel, the map is an x y scatter chart with an […]
This article demonstrates how to use drop down lists combined with an Excel defined Table and a chart. This allows […]
This article demonstrates how to change chart series while hovering with mouse cursor over a series name. The image above […]
I will in this article demonstrate how to highlight a group of values plotted in an x y scatter chart […]
What if you want to show a selection of a data set on a chart and easily change that selection? […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
What's on this page Copy a file Copy and rename a file Rename a file List files in a folder […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
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:
Maybe 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:
to