CODEWITHSUNDEEP
vbaexcelcharts
Ignatius S. Millan
Ignatius S. Millan

Reputation: 3

Get series name from a chart by clicking on that series

I want to get from an specific chart the time series name by clicking on it. I have something like this:

Sub CallSerie()

CallingShapeName = ActiveSheet.Shapes(Application.Caller).Name

MsgBox CallingShapeName

End Sub

But this throws the chart name, not the series name.

Upvotes: 0

Views: 813

Answers (1)

Rory
Rory

Reputation: 34075

You don't want to assign a macro to the chart - it's better to use its events. The following code assumes you have one chart embedded in a worksheet, and the code needs to go into that worksheet's code module:

Private WithEvents cht As Excel.Chart
Private Sub cht_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim IDNum As Long
    Dim a As Long
    Dim b As Long

    cht.GetChartElement x, y, IDNum, a, b

    If IDNum = xlSeries Then MsgBox cht.SeriesCollection(a).Name
End Sub

Private Sub Worksheet_Activate()
    HookChart
End Sub

Public Sub HookChart()
    Set cht = Me.ChartObjects(1).Chart
End Sub

Then simply activate a different sheet and switch back to the chart sheet (or just run the HookChart routine), then click a chart series.

Upvotes: 2

Related Questions