Need event for selecting a series on a chart

[Giff]

I have an embedded chart in sheet 1 of my workbook. I am looking to
put together an event driven macro depending on which line from the
graph is clicked on.

I have read Chris Pearsons page on event procedures and ChartObject
objects (http://www.cpearson.com/excel/Events.aspx), however this does
not allow me to work from the SeriesCollection object.

I usually like to add the code I am currently using, however with this
I am at a loss and have nothing.

Giff

 

[Bernie Deitrick]

Giff,

AFAIK, what you describe has been and continues to be a shortcoming of the Excel object - it was the
source of my first major frustration with Excel. A workaround is to use a macro to display a
userform with a list of the names of the lines displayed for the user to select for further
processing.

HTH,
Bernie
MS Excel MVP

 

[Jon Peltier]

Here's an article about chart events:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

- Jon

 

[Bernie Deitrick]

Jon,

Very cool. Is there a way to identify the point that is currently selected when a macro is run,
i.e., not using the mouse click event?

Bernie

 

[Peter T]

Search "whichpoint" in this ng

Regards,
Peter T

Jon,

Very cool. Is there a way to identify the point that is currently selected when a macro is run,
i.e., not using the mouse click event?

Bernie

 

[Jon Peltier]

You have to rely on XLM for that:

Sub WhatPoint()
Dim ptSelected As Point
Dim sSelection As String
Dim iSrs As Long
Dim iPt As Long
If TypeName(Selection) = "Point" Then
Set ptSelected = Selection
sSelection = ExecuteExcel4Macro("selection()")
iSrs = Val(Mid(sSelection, 2))
iPt = Mid(sSelection, InStr(sSelection, "P") + 1)
MsgBox "You've selected Series " & iSrs & " Point " & iPt
End If
End Sub

- Jon