Get chart point value macro
 

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

Get chart point value macro
 

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
_______


"joecrabtree" wrote in message
...
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

Get chart point value macro
 

On Nov 21, 9:00 pm, "Jon Peltier"
wrote:
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
_______

"joecrabtree" wrote in message

...



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- Hide quoted text -

- 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

Get chart point value macro
 

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
_______


"joecrabtree" wrote in message
...
On Nov 21, 9:00 pm, "Jon Peltier"
wrote:
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
_______

"joecrabtree" wrote in message

...



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- Hide quoted text -

- 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

Get chart point value macro
 

On Nov 22, 2:41 pm, "Jon Peltier"
wrote:
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
_______

"joecrabtree" wrote in message

...



On Nov 21, 9:00 pm, "Jon Peltier"
wrote:
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
_______

"joecrabtree" wrote in message

...

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- Hide quoted text -

- 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- Hide quoted text -

- 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

Get chart point value macro
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"joecrabtree" wrote in message
...
On Nov 22, 2:41 pm, "Jon Peltier"
wrote:
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
_______

"joecrabtree" wrote in message

...



On Nov 21, 9:00 pm, "Jon Peltier"
wrote:
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
_______

"joecrabtree" wrote in message

...

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- Hide quoted text -

- 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- Hide quoted text -

- 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