Get chart point value macro

[joecrabtree]

To all,

I have a xy chart in excel. Is there anyway by writing a macro, that
the user can click on two points on the graph, and then it calculates
the gradient between them?


Thanks


Joe Crabtree

 

[Jon Peltier]

Joe -

This article describes how to use chart events to get information from the
chart:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

You need to make the code a little smarter, so that a left click indicates
the first point and a right click the second, or something similar.

- Jon

 

[joecrabtree]

Joe -

This article describes how to use chart events to get information from the
chart:

http://www.computorcompanion.com/LPMArticle.asp?ID=221

You need to make the code a little smarter, so that a left click indicates
the first point and a right click the second, or something similar.

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







- Show quoted text -

Thanks for that.

I am using:

Private Sub Chart_MouseUp(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)

' 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

End Sub


However what I would like to do is store the myx and myy variables in
a table on worksheet 'data1' as the article sugested it was possible
to do. I only need the value for two points (Two mouse clicks). Could
you show me how to do this?

Thanks for your help,

Regards

Joseph Crabtree

 

[Jon Peltier]

Instead of the messagebox, use something like this:

With ActiveWorkbook.Worksheets("Data").Range("C2")
If Len(.Value) = 0 then
' C2 is empty, populate C2 and D2
.resize(, 2).value = Array(myX, myY)
Else
' C2 is filled, populate C3 and D3
.offset(1).resize(, 2).value = Array(myX, myY)
End If
End With

- Jon

 

[joecrabtree]

Instead of the messagebox, use something like this:

With ActiveWorkbook.Worksheets("Data").Range("C2")
If Len(.Value) = 0 then
' C2 is empty, populate C2 and D2
.resize(, 2).value = Array(myX, myY)
Else
' C2 is filled, populate C3 and D3
.offset(1).resize(, 2).value = Array(myX, myY)
End If
End With

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
















- Show quoted text -

Thanks for that. I got that to work fine. However I now want to use it
for an embedded chart in a worksheet. I have got this to work for the
simple version in the linked example below: However this doesn't allow
me to modify this to include the same code as I was using in the un-
embedded chart. Is there anyway I can modify the code below to
display the x and y values when a point is clicked, and then place
them in worksheet 'data' as above?

Thanks

Option Explicit

' Declare object of type "Chart" with events
Public WithEvents EvtChart As Chart

Private Sub EvtChart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)

MsgBox "Element: " & ElementID & vbCrLf & " Arg 1: " & Arg1 _
& vbCrLf & " Arg 2: " & Arg2

End Sub

 

[Jon Peltier]

Why don't you post at the top of the message? That's the long-standing
convention in these newsgroups, and it makes following the thread easier if
everyone follows the same convention.

I think all you need to do is change the chart reference in the With/End
With block, then swap out the MsgBox in favor of writing to the cells:


Private Sub EvtChart_MouseUp(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 EvtChart
' 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
End Sub


- Jon