How to set a Click event for a bar in a barchart ? RRS feed


  • You have to create an event variable for the chart and you have to initialize it by yourself. In the code below the variable MyChart is set when you activate the sheet.

    The "bar" does not exists in a bar chart, charts only have series. And there is no Click event, but a Select event.

    Inside the event routine the ElementID gives you the information what is selected and the arguments more detailed informations.

    To make the code below work, create a simple bar chart within a sheet, copy the code into the code module of that sheet, switch to an other sheet an back.


    Option Explicit
    Dim WithEvents MyChart As Chart
    Private Sub MyChart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
      Select Case ElementID
        Case xlSeries
          MsgBox "Series " & Arg1 & " Point " & Arg2
        Case Else
          Debug.Print ElementID
      End Select
    End Sub
    Private Sub Worksheet_Activate()
      Set MyChart = Me.ChartObjects(1).Chart
    End Sub

    Friday, June 5, 2015 4:30 AM