Change chart series by clicking on data [VBA]
The image above shows a chart populated with data from an Excel defined Table. The worksheet contains event code that changes the shown cart series based on which cell is selected. See the animated image below.
You can also select multiple cells in the table by click and hold with the left mouse button and then drag with the mouse to select multiple cells.
If you want to select cells that are not adjacent you can press and CTRL key and then click on the cells you want to select. The corresponding chart series shows up in the chart automatically, this is made possible with event code.
VBA code
'Event code Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'Dimension variables and declare data types Dim ACell As Range Dim ActiveCellInTable As Boolean Dim c As Single Dim str As String 'Iterate every cell in selection For Each ACell In Target 'Enable error handling On Error Resume Next 'If selected cell is in a table and the table name is Table1, save TRUE in ActiveCellInTable (boolean) ActiveCellInTable = (ACell.ListObject.Name = "Table1") 'Resume normal error handling (stop if an error occurs) On Error GoTo 0 If ActiveCellInTable = True Then 'Save cell reference (First column in table) if str is empty If str = "" Then str = "Table1[[#ALL]," & ACell.ListObject.Range.Cells(1, 1).Value & "]" 'Calculate selected cell's column number in table c = ACell.Column - ACell.ListObject.Range.Cells(1, 1).Column + 1 'Check if column number is above 1 If c > 1 Then 'Add cell reference str = str & "," & "Table1[[#ALL]," & ACell.ListObject.Range.Cells(1, c).Value & "]" 'Change "Chart 1" data source ChartObjects("Chart 1").Chart.SetSourceData Source:=Range(str) End If End If Next ACell End Sub
Where to put the code?
- Press Alt + F11 to open the Visual Basic Editor.
- Right click on sheet name Sheet1.
- Left click on "View Code".
- Copy/Paste VBA code to sheet module.
- Exit Visual Basic Editor and return to Excel.
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
How to use mouse hover on a worksheet [VBA]
I recently discovered an interesting technique about using a user defined function in a HYPERLINK function. Jordan at the Option […]
Change chart data range using a Drop Down List [VBA]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
11 Responses to “Change chart series by clicking on data [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,
Happy to see above code but when i try to incorporate the above code in to excel its not working kinldly guide
Best Regards
Rahul jadhav
Rahul Jadhav,
What happens?
Hi Oscar,
Glad to see your reply.
i would like to tell you that i downloaded your file and checked the macro and its working fine.
would like to say few words regarding this website, you have develope it very nicely. i am working on excel from last 7-8 years and i have never come across any website like this. good job done and its really very helpful to understand different Scenario.
good work and best wishes
Rahul Jadhav
India
Rahul Jadhav,
Thank you!
Hello Oscar,
Thanks for sharing the excel options
I am getting the error message, when i have more than 10 columns on selection
saying range of object worksheet failed
debbug shows in last line
Thanks
narayan
Narayan,
can you provide a workbook?
Upload here
Dear Sir,
I was trying to figure out how this code actually works, as since I am very new to VBA coding I was not able to understand why the code always makes its own chart even if you provide your axis series.
My data is in the reverse form as in North East West South are in column not in rows.
Hope you would help.
If you want to change the row instead of the column, what will the VBA code be?
MAHMOUD,
It is not possible to reference a row in an Excel Table using structured references, at least no with my approach.
I recommend that you copy and with "Paste Special..." transpose the Excel table so the rows become columns and columns become rows.
You also need to create an Excel Table with the new values before trying the macro again.
MAHMOUD
Here is an image.
I also renamed the Table1 to Table2 and the new Table to Table1.
I NEED MAKE CHART BY MONTH NOT BY NORTH OR SOUTH