I want to be able to create a graph, with the top left of it being on my cursor position. Is that possible? Can the (X,Y) of my mouse be converted into a range format?
Add a comment
|
2 Answers
Hm, it's not exactly built in AFAIK, but I found this page which gives a suggestion that worked for me:
In a module, put this at the top:
Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Private Declare Function ScreenToClient Lib "user32" (ByVal hWnd As Long, _
lpPoint As POINTAPI) As Long
Private Type POINTAPI
X As Long
Y As Long
End Type
Then, for the subroutines to get the mouseX and mouseY, put this somewhere below:
Function MouseX(Optional ByVal hWnd As Long) As Long
' Get mouse X coordinates in pixels
'
' If a window handle is passed, the result is relative to the client area
' of that window, otherwise the result is relative to the screen
Dim lpPoint As POINTAPI
Application.Volatile(false)
GetCursorPos lpPoint
If hWnd Then ScreenToClient hWnd, lpPoint
MouseX = lpPoint.X
End Function
and
Function MouseY(Optional ByVal hWnd As Long) As Long
' Get mouse Y coordinates in pixels
'
' If a window handle is passed, the result is relative to the client area
' of that window, otherwise the result is relative to the screen
Dim lpPoint As POINTAPI
Application.Volatile(false)
GetCursorPos lpPoint
If hWnd Then ScreenToClient hWnd, lpPoint
MouseY = lpPoint.Y
End Function
Then, in Excel, if you simply enter into a cell =mouseX() it'll return the mouseX position when you hit ENTER. Same with =mouseY().
Trying it out, I did:
Sub chart_Test()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveSheet.Shapes("Chart 1").Top = MouseY()
ActiveSheet.Shapes("Chart 1").Left = MouseX()
End Sub
and got it to work.
edit: Note, I'm not as good with charts as other things in VBA, so as you create charts, you'll need to edit the .Shapes("Chart 1"). part to whatever chart name/number you're on. Or iterate through them.
Comments
Not sure about the mouse x y but you could get the range on worksheet selection change. Put the chart at that location.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Chart.Left = Target.column
Chant.Top = Target.row
End Sub
2 Comments
BruceWayne
While it's not what OP asked for, I think this would be your better bet. The mouseX/mouseY may work, but if you can do it, I would define the chart location by a cell's location, instead of mouseX/mouseY because your mouse has to be exactly where you want it, when you run the macro (in the code I posted anyways). Nice thinking @matthewD
Lumigraphics
Remember that using Windows library functions makes your code useless if it needs to run on Excel for Mac. A pure VBA solution is likely going to be supported on both platforms.