OnClick event to select and display a chart by clicking on a cell containing text

You can use hyperlinks to trigger some specific action by using the Worksheet_FollowHyperlink event.

In the worksheet code module for the sheet where the user will be clicking cells:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    
    Debug.Print Target.TextToDisplay   'Text of the clicked cell
    Debug.Print Target.Range.Address   'the actual cell which was clicked

    'Process either of the above to figure out what action to take...

End Sub

And in a regular module:

'Sets up your links: select one or more cells and run this to turn the cell 
'  contents into hyperlinks which will trigger the event handler.
Sub MakeLinks()
    Dim c As Range
    For Each c In Selection.Cells
        c.Hyperlinks.Delete 'remove any existing link
        c.Worksheet.Hyperlinks.Add Anchor:=c, Address:="#LinkTarget()", _
                                   TextToDisplay:=c.Text
        'you can re-style the cells if you don't want the blue/underlined font
    Next c
End Sub


'This serves as a "dummy" destination for the hyperlinks
'  Just returns the clicked-on cell, so the selection doesn't jump around
Function LinkTarget() As Range
    Set LinkTarget = Selection
End Function

Once this is set up, clicking any of the links will trigger the event handler in the sheet module, and you can decide what action to take based on the text of the clicked link.

This is a useful alternative to having a bunch of buttons in a table-like structure, since you don't have to worry about tying buttons to specific rows (eg. when sorting)

Notes

• If you need to respond to clicks on multiple sheets then there's also the Workbook_SheetFollowHyperlink event (in the ThisWorkbook code module)
• This doesn't work with links created using the HYPERLINK() worksheet formula, since those type of links don't trigger the event handler.
• If your cell text is short then clicking the cell may "miss" the link - in that case you can pad the text with spaces on either side so it fits the cell better

You can run a macro when a certain cell is selected by using the Worksheet.SelectionChange event.

I would start by putting the event handler into the sheet in question and test what the selected range is compared to the range you care about.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.row = row_of_kpi_desc And Target.Column = col_of_kpi_desc Then
        Call Other_Macro
    End If
End Sub

Other_Macro could copy the chart from this other hidden sheet and paste it to a specific location. There are ample examples of how to do this. Best of luck.

Assuming the charts are on a worksheet names Charts, and each chart object has been named with a useful name (my code uses "KPI 1", "KPI 2", "KPI 3", but feel free to use something descriptive).

This code goes in the code module behind the sheet the user will click on. To access it easily, right-click on the sheet tab and select View Code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Cells.Count = 1 Then
    Select Case True
      Case Not Intersect(Target, Me.Range("A2")) Is Nothing
        Dim ChartName As String
        ChartName = "KPI 1"
      Case Not Intersect(Target, Me.Range("A3")) Is Nothing
        ChartName = "KPI 2"
      Case Not Intersect(Target, Me.Range("A4")) Is Nothing
        ChartName = "KPI 3"
    End Select
    
    If Len(ChartName) > 0 Then
      Application.ScreenUpdating = False
      Dim shp As Shape
      For Each shp In Me.Shapes
        If shp.Name = "KPI Chart" Then
          shp.Delete
        End If
      Next
      Worksheets("Charts").ChartObjects(ChartName).Copy
      Me.Paste
      With Me.Shapes(Me.Shapes.Count)
        .Top = Target.Top
        .Left = Target.Left + Target.Width
        .Name = "KPI Chart"
      End With
      ActiveCell.Select
      Application.ScreenUpdating = True
    End If
  End If
End Sub