Detect selected chart point

[Guest]

I want to detect by code what datapoint in an XY chart is clicked on by the
user, i.e. series collection ID and then data point ID within the series
collection. Additionally, I want to PREVENT the series collection from being
selected as a result of the user click.

What I'm trying to do is to display a XY chart, and when the user selects
any one point, I want to display additional information about that one point.
When the user selects a different point, show the information about that
other point, etc.

I've got a routine that will tell me the X and Y screen coordinates of a
user click, but I'm not looking forward to translating that into chart
coordinates, and then deciphering the data point from the chart coordinates
and I want to know if there is a more direct approach.

Finally, how do I prevent the series collection from being selected as a
result of the click?

Thanks,
-mark

 

[Peter T]

Hi Mark,

If you have a typical chart, one chart group, no deleted series, you might
be able to use something simple like this:

Private Sub Chart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim msg As String

Select Case ElementID
Case xlSeries
msg = "Series " & Arg1
If Arg2 > 0 Then msg = msg & vbCr & "Point " & Arg2

If Arg1 = 2 Then 'series 2
'deselect series 2
Me.ChartArea.Select
msg = "Don't select series 2"
End If
'Case: 'other items?

End Select
If Len(msg) Then MsgBox msg

End Sub

Above is in a Chart module and assumes your chart is a chart sheet. If a
chart object on a worksheet you would need to create your own "With Events
myChart as excel.chart" Class, which will give you the same events.

Be aware that what you think is say Series 2 is not always necessarily
Seriescollection(2). You may find the old XLM Selection function more useful
to identify Series & Point. Search this ng for "WhichPoint", I think
originally by Stephen Bullen. You can link that into the chart select event.

I don't think you can prevent selection of a particular series but should be
able to select something else, eg ChartArea as in the above example.

Regards,
Peter T