How do I get the (X,Y) coordinate of my cursor on a worksheet?
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.
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