How do I get the (X,Y) coordinate of my cursor on a worksheet?

Question

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?

Answer 1

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.

Answer 2

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