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!