VBA Return datapoint on embedded chart

Almost solved it. the mouseup event seems to only work when anything but the data series is selected. Tried mouse down and viola

Here's the class module http://www.computorcompanion.com/LPMArticle.asp?ID=221 and where I found the ideas

Public WithEvents myChartClass As Chart

Private Sub myChartClass_Mousedown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Did we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
' Extract x value from array of x values
myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)
' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)
MsgBox (Arg1 & Chr(10) & Arg2)
End If
End If
End With
End Sub

Now to enable selection of more than one chart on the worksheet.

Any tips?

ziggy

After much experimentation - i finally got it to work. Posted for anyone else having a crack.

This goes in a module
Dim myClassModule() As New EventClassModule

Sub InitializeChart()
If ActiveSheet.ChartObjects.Count > 0 Then
ReDim myClassModule(1 To ActiveSheet.ChartObjects.Count)

Dim chtObj As ChartObject
Dim chtnum As Integer

For Each chtObj In ActiveSheet.ChartObjects
chtnum = chtnum + 1
Set myClassModule(chtnum).myChartClass = chtObj.Chart
Next
End If
End Sub

Sub ResetCharts()
Dim chtnum As Integer

For chtnum = 1 To UBound(myClassModule)
Set myClassModule(chtnum).myChartClass = Nothing
Next
End Sub

This goes in the class module

Public WithEvents myChartClass As Chart



Private Sub myChartClass_Mousedown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)

Dim ElementID As Long, Arg1 As Long, Arg2 As Long
Dim myX As Variant, myY As Double

With ActiveChart
' Pass x & y, return ElementID and Args
.GetChartElement x, y, ElementID, Arg1, Arg2

' Did we click over a point or data label?
If ElementID = xlSeries Or ElementID = xlDataLabel Then
If Arg2 > 0 Then
' Extract x value from array of x values
myX = WorksheetFunction.Index _
(.SeriesCollection(Arg1).XValues, Arg2)
' Extract y value from array of y values
myY = WorksheetFunction.Index _
(.SeriesCollection(Arg1).Values, Arg2)
MsgBox (Arg1 & Chr(10) & Arg2)
End If
End If
End With
End Sub

Thank you very much for this post, Ziggy12. Help me a lot.
P.S.: Sorry my english.

can you please post your final Excel file? As a newbie, I am having trouble following your code.

kellsens said:

Thank you very much for this post, Ziggy12. Help me a lot.
P.S.: Sorry my english.

Click to expand...