Working with cursor position in Excel/VBA
Occasionally you might need to know the mouse position, for example if you need to position a form there. An example of a project using this technique is here jSon and Dynamic Forms.
Positioning
To be able to find out the mouse position, we have to use calls to the Windows API. Luckily talking to Windows API is rather straightforward. The first step is to declare the functions we are going to use, and which windows library to find them.
Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hdc As Long, _ ByVal nIndex As Long) As Long Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, _ ByVal hdc As Long) As LongGetDC and ReleaseDC return and release a handle that can be used to handle the ‘device context’, and GetDeviceCaps returns device specific parameters for that context.
Pixels versus points
We are going to use this to return 2 device specific parameters which are identified by these constants. This will give us the pixels per inch on the monitor. We need this because mouse position is expressed in pixels, but form positioning is expressed in points/inch. So to be able to translate one to the other we need to know the currently selected resolution.Const LOGPIXELSX = 88 Const LOGPIXELSY = 90 Public Function pointsPerPixelX() As Double Dim hDC As Long hDC = GetDC(0) pointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX) ReleaseDC 0, hDC End Function Public Function pointsPerPixelY() As Double Dim hDC As Long hDC = GetDC(0) pointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY) ReleaseDC 0, hDC End Function
Mouse position
Now that we know how to convert between pixels and points, the next step is get the cursor position in pixels. This is returned as a composite Type that includes the X and Y (or left and top) co-ordinates of the mouse, using the GetCursorPos Windows API call
Public Type tCursor left As Long top As Long End Type Private Declare Function GetCursorPos Lib "user32" (p As tCursor) As Long Public Function WhereIsTheMouseAt() As tCursor Dim mPos As tCursor GetCursorPos mPos WhereIsTheMouseAt = mPos End Function
Converting pixels to points
Putting all that together we can now return the mouse position coverted to points so that we can position a userform where the mouse it positioned. Here is the complete code, where convertmousetoform() will return a tCursor pair that can be used as the top and left property of a userform.Option Explicit ' these are special function to get device specific things Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long Declare Function GetDeviceCaps Lib "Gdi32" (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Const LOGPIXELSX = 88 Const LOGPIXELSY = 90 ' we need to be able to find cursor position where mouse was clicked Public Type tCursor left As Long top As Long End Type Private Declare Function GetCursorPos Lib "user32" (p As tCursor) As Long Public Function pointsPerPixelX() As Double Dim hDC As Long hDC = GetDC(0) pointsPerPixelX = 72 / GetDeviceCaps(hDC, LOGPIXELSX) ReleaseDC 0, hDC End Function Public Function pointsPerPixelY() As Double Dim hDC As Long hDC = GetDC(0) pointsPerPixelY = 72 / GetDeviceCaps(hDC, LOGPIXELSY) ReleaseDC 0, hDC End Function Public Function WhereIsTheMouseAt() As tCursor Dim mPos As tCursor GetCursorPos mPos WhereIsTheMouseAt = mPos End Function Public Function convertMouseToForm() As tCursor Dim mPos As tCursor mPos = WhereIsTheMouseAt mPos.left = pointsPerPixelY * mPos.left mPos.top = pointsPerPixelX * mPos.top convertMouseToForm = mPos End Function