Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I link comments on the chart to a point on the chart? | Charts and Charting in Excel | |||
Line and Point in the same chart? | Charts and Charting in Excel | |||
bar chart starting point | Charts and Charting in Excel | |||
Chart Point Analysis | Charts and Charting in Excel | |||
Import chart to Power Point and Macro problem | Excel Discussion (Misc queries) |