Get chart point value macro

  • Thread starter Thread starter joecrabtree
  • Start date Start date
J

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
 
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 -
Click to expand...

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
 
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
 
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 -
Click to expand...

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
 
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
 
Back
Top