VBA Return datapoint on embedded chart

[Ziggy12]

I want to return which data point is select on an embedded chart. My code almost works. The message box returns the series selected (ARG1) but the point (ARG2) returns -1 on the first click then the datapoint selected on a second click after the data series has been selected by the first click.

Somehow I need to select the data series then the datapoint.

I've tried a mousedown and a mouseup but they haven't worked and do't return ARG1 and ARG2 anyway.

Any ideas?

ziggy

This is in the module
Dim myClassModule As New EventClassModule

Sub InitializeChart()
Set myClassModule.myChartClass = _
Worksheets(1).ChartObjects(1).Chart

End Sub

This is in the class module
Public WithEvents myChartClass As Chart

Private Sub myChartClass_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
ActiveChart.SeriesCollection(1).Select
MsgBox (Arg1 & Chr(10) & Arg2)
End Sub

 

[Ziggy12]

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

 

[Ziggy12]

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

 

[kellsens]

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

 

[barnis]

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

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