SeriesCollection object

M

Mike Archer

Hello. Is there a way to get the name or index of a series collection that
has been clicked?
Something like:
MsgBox ActiveChart.SeriesCollection.Selected.Name
or
MsgBox ActiveChart.SeriesCollection.Selected.Index

Of course, those examples don't work.

Thanks,
Mike
 
P

Peter T

Is that for a chart-sheet or embedded chart. If the latter, do you want to
trap the selection event of just one chart or all charts on a sheet or all
in a workbook.

Regards,
Peter T
 
M

Mike Archer

The charts are on a worksheet. I would like the event to run when the user
clicks on a series label (like trying to change the text in the label).
 
P

Peter T

You didn't answer the question "If the latter..." but have a go with this -

into a normal module and a class module named as indicated

''' in a normal module
Private colCharts As Collection

Sub StartChartEvents()
Dim i As Long
Dim sht As Object
Dim chtObj As ChartObject
Dim cls As clsChtEvents

Set colCharts = New Collection
For Each sht In ActiveWorkbook.Sheets
For i = 1 To sht.ChartObjects.Count
Set cls = New clsChtEvents
Set cls.cht = sht.ChartObjects(i).Chart
colCharts.Add cls
Next
Next

End Sub

''' in a class named clsChtEvents

Public WithEvents cht As Excel.Chart

Private Sub cht_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim s As String
Dim sr As Series
Select Case ElementID
Case xlSeries
Set sr = cht.SeriesCollection(Arg1)
If Arg2 > 0 Then s = " Point " & Arg2
MsgBox cht.Parent.Parent.Name & " " & cht.Parent.Name & _
vbCr & sr.Name & s
End Select
End Sub


Run StartChartEvents, then click on any series in any chart

Regards,
Peter T
 
Top