Author: Oscar Cronquist Article last updated on October 01, 2018

The following vba code makes it possible to automatically update a chart when you click a cell in a table.

See animated gif.

You can also select multiple cells in the table (Press and hold Ctrl when selecting cells).

Vba code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
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
    'Don´t stop if an error occurs
    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
  1. Right click on sheet name: Sheet1
  2. Left click on "View Code"
  3. Copy/Paste vba code to module
  4. Return to excel

Download excel *.xlsm file
Select a cell in a table to update a chart.xlsm