Get X Axis value on the Click of Chart - Excel VBA

问题:

I have come across a strange requirement.

I need to get X-Axis value from chart when user clicks on Chart Area.

I know we can assign a macro to a chart. So in way, event for the chart can be created. But no idea of how to proceed further.

Any idea to do this please?

Thanks.

回答1:

If your Chart is in a Chart Sheet then you can right click on the Chart sheet tab, select "View code" and paste the following in its code module (see screenshot below)

Option Explicit

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Select Case ElementID
    Case xlSeries
       If Arg2 > 0 Then
           MsgBox "Series " & Arg1 & vbCr & "Point " & Arg2
       End If
    Case Else
       MsgBox Arg1 & vbCr & Arg2
    End Select
End Sub

If your chart is embedded in a sheet then you will have to use WithEvents as @brettdj has already covered here

FOLLOWUP

Is this what you are trying?

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
    Select Case ElementID
    Case xlSeries
        If Arg2 > 0 Then
            x = ActiveChart.SeriesCollection(Arg1).XValues
            For i = LBound(x) To UBound(x)
                If i = Arg2 Then
                    MsgBox "Point :" & i & "and X Value = " & x(i)
                    Exit For
                End If
            Next i
       End If
    End Select
End Sub