Author: Oscar Cronquist Article last updated on August 21, 2019

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?

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Right click on sheet name Sheet1.
  3. Left click on "View Code".
  4. Copy/Paste VBA code to sheet module.
  5. Exit Visual Basic Editor and return to Excel.

Download Excel file


 * You will also get a weekly newsletter, unsubscribe anytime!

Related articles

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 [โ€ฆ]

How to create an interactive Excel chart [VBA]

This article describes how to create an interactive chart, the user may click on a button or multiple buttons and [โ€ฆ]

How to create a dynamic chart

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: [โ€ฆ]

Dynamic data labels in a chart

My fascination for charts and vba continues (see previous posts). This post demonstrates how to highlight a group of values [โ€ฆ]

Use a map in an Excel chart

This post describes how to add a map (background picture) to a chart. The following animated picture shows a scatter [โ€ฆ]

Interactive candlestick chart

Rudy asks in this post Use a mouse hovering technique to create an interactive chart: Is it possible to create [โ€ฆ]