Click on Chart to identify series/point

[mcget]

Hi,

I know that you can use a chart click event to do this, but i just cannot get it to work.

What i need to do, is by clicking on a chart series, i can get the chart point name input into a cell, say a2.

I think the code below if fine, i just dont know how to enter the correct things after the call function below, (???).

Public WithEvents calchart As chart
Private Sub calchart_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
Dim ElementID As Long
Dim Arg1 As Long
Dim Arg2 As Long
Dim chart_label As Variant
Dim chrt As chart
Dim ser As Series
Dim activity As String

On Error Resume Next
'use the GetChartElement to return information about the part of the chart that has
'been clicked on by the user
calchart.GetChartElement x, y, ElementID, Arg1, Arg2
'assign the series names to an array
Set chrt = ActiveChart
Set ser = ActiveChart.SeriesCollection(1)
chart_label = ser.XValues

If ElementID = xlSeries Then
activity = chart_label(Arg2)
Call identify(???)
End If
End Sub

 

[Darren Bartrup]

Have a look at this link: The Spreadsheet Page Excel Downloads: A Class Module to Manipulate a Chart SERIES: A Class Module To Manipulate A Chart Series

John Walkenbach has done all the work for you.

 

[mcget]

thank you, but this isnt what i meant. What i need is the ability to click on the actual bar in a chart to perform the action of populating a cell with the point name!

 

[Darren Bartrup]

This may help some more then:
Chart Events in Microsoft Excel

It contains info for chart sheets and embedded charts and as it's by Jon Peltier I'd trust it with my life as far as charts go. :D

 

[barnis]

This example uses a Chart sheet. I got it to work great. Does anyone know the specifics to modify it such that it works for an Embedded Chart too? I have seen examples but never with enought step-by-step to actually get it to work for me...