You are jumping in at the deep end if you are wanting to code against the charts events
If you use a chart sheet then you can write code within the chart events of the chart sheet.
Not that you will need to click the first data point twice in order to identify individual data points.
If you use a chartobject then you will need to create a class to expose those same chart events.
standard code module
Public gChtEvt As CChtEvnt
Sub StartListeningToChart()
Set gChtEvt = New CChtEvnt
With ThisWorkbook.Worksheets("Sheet1")
Set gChtEvt.UseChart = .ChartObjects(1).Chart
Set gChtEvt.OutputToCell_X = .Range("A1")
Set gChtEvt.OutputToCell_Y = .Range("B1")
Set gChtEvt.OutputToCell_Name = .Range("C1")
End With
End Sub
class module, CChtEvnt
Option Explicit
Private WithEvents mCht As Chart
Private mOutputX As Range
Private mOutputY As Range
Private mOutputName As Range
Public Property Set UseChart(RHS As Chart)
Set mCht = RHS
End Property
Public Property Set OutputToCell_X(RHS As Range)
Set mOutputX = RHS
End Property
Public Property Set OutputToCell_Y(RHS As Range)
Set mOutputY = RHS
End Property
Public Property Set OutputToCell_Name(RHS As Range)
Set mOutputName = RHS
End Property
Private Sub mCht_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
If ElementID = xlSeries Then
mOutputName = mCht.SeriesCollection(Arg1).Name
If Arg2 > 0 Then
mOutputX = Application.Index(mCht.SeriesCollection(Arg1).XValues, Arg2)
mOutputY = Application.Index(mCht.SeriesCollection(Arg1).Values, Arg2)
Else
' whole series selected
mOutputX = ""
mOutputY = ""
End If
End If
End Sub
There is a button on the worksheet that runs the code to initiate the class to listen for chart events.
Bookmarks