Extract X data from Chart

[billinr]

I have looked through the information provided by this group for chart
events, and I have a good code to get the embedded chart to show the message
box with the point information. (Thanks Jon Peltier)
What I would like to do now is to extract that X data and paste it into
another area of the workbook to run some other analysis.

Is there some way to have the X value be copied into another cell on another
sheet?

I am using this code in a MouseUp event:


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)

' Display message box with point information
MsgBox "Series " & Arg1 & vbCrLf _
& """" & .SeriesCollection(Arg1).Name & """" & vbCrLf _
& "Point " & Arg2 & vbCrLf _
& "X = " & myX & vbCrLf _
& "Y = " & myY
End If
End If
End With



Thanks for the help

 

[Jon Peltier]

Replace the message box code with:

Worksheets("My Sheet").Range("A3").Value = myX
Worksheets("My Sheet").Range("B3").Value = myY

- Jon

 

[billinr]

Perfect!
Thanks AGAIN, Jon.

Replace the message box code with:

Worksheets("My Sheet").Range("A3").Value = myX
Worksheets("My Sheet").Range("B3").Value = myY

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______