VBA: How to know where the mouse pointer is?

As the left click generally selects a cell, you can use the Selection or ActiveCell objects to get specific properties of the cell the left click was last performed on (Selection.Row, Selection.Column, ActiveCell.Font, etc).

If this isn't sufficient, I'm sure with some more information about what you're looking for, the other members of the board could list multiple ways of getting any information you want from a selected, or active cell, or the click event itself.

Never heard of any functionality tracking the mouse position withing excel or vba, however.

G_Seattle said:

Is there a way of knowing where the mouse pointer is, as is what cell is it currently over?

Click to expand...

You know what, Jaafar and I have been going back and forth on similar issues for some time now, and he devised a really special method of conditional formatting based on mouse move over naked cells.

Below is his code, modified to show the "moused over" cell address in cell A1.

Note, 99.9999% of this code is Jaafar's braintrust; I only did a slight modification for the address display in A1.


In a standard module:

Option Explicit

Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

Type POINTAPI
x As Long
Y As Long
End Type

Dim lngCurPos As POINTAPI
Dim TimerOn As Boolean
Dim TimerId As Long
Public oldColor As Long
Dim newRange As Range
Dim oldRange As Range

Sub StartTimer()
If Not TimerOn Then
TimerId = SetTimer(0, 0, 0.01, AddressOf TimerProc)
TimerOn = True
Else
MsgBox "Timer already On !", vbInformation
End If
End Sub

Sub TimerProc()
On Error Resume Next
GetCursorPos lngCurPos
Set newRange = ActiveWindow.RangeFromPoint(lngCurPos.x, lngCurPos.Y)
If newRange.Address <> oldRange.Address Then Range("A1").Value = newRange.Address
Set oldRange = newRange
End Sub

Sub StopTimer()
If TimerOn Then
KillTimer 0, TimerId
TimerOn = False
Else
MsgBox "Timer already Off", vbInformation
End If
End Sub


In the worksheet module:

Option Explicit

Dim TrgtColor As Long
Dim oldTarget As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.ColorIndex = TrgtColor
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set oldTarget = Target
TrgtColor = oldColor
End Sub


To activate the code, run the "StartTimer" macro and to stop it, run the "StopTimer" macro.

Thanks again to Jaafar, aka screen name rafaaj2000.

Well that is very cool. Care to explain how it works for us newbies? Dave

The short answer is, it uses API calls to identify and then translate the x and y coordinates of the mouse pointer into the corresponding cell address. See these links for background on the issue, which with Jaafar's persistence gave rise to his posted solution dealing with conditional formatting, and which was modified here for cell address.


http://www.mrexcel.com/board2/viewtopic.php?t=77296

http://www.mrexcel.com/board2/viewtopic.php?t=90022

http://www.mrexcel.com/board2/viewtopic.php?t=90381

"Impossible and cannot be done. Excel does not support that functionality over naked cells. "
"I'll send you a 12-pack of locally brewed Anchor Steam Beer and a couple loaves of genuine sourdough bread, fresh from the markets at Fisherman's Wharf here in San Francisco"
Tom I believe the above quotes are in order. Thanks for the info!
G_Seattle...I hope you have enjoyed this too. Dave
ps. Jaafar should perhaps enjoy the spoils?

Those quotes have indeed come back to find me, but I don't mind being wrong because it was such a good solution. In fact I did prepare and send Jaafar a full box of San Francisco treats just as I promised. The unfortunate part of the story is, the shipments kept getting refused at customs because no shipping company would send food and liquor to a location outside the United States (I am in California and the destination was UK). Anyway, the code is excellent and is indeed much appreciated.

thanks guys!

One thing i noticed, and this can probably be said for any macro, was that while the code was running, switching to a different workbook would overwrite cell A1 in that workbook as well. I'm sure the code, when in use, would be turned on for a short period and then turned off. But for those not thinking while testing it out (not that that would have happened to me ), might want to be careful.

Added PtrSafe, but this code from post #3 above still gives a type mismatch on red text (AddressOf TimerProc) in Win10/64-Bit Excel 2010. Can anyone determine a remedy?

Sub startTimer()
If Not TimerOn Then
TimerId = SetTimer(0, 0, 0.01, AddressOf TimerProc)
TimerOn = True
Else
MsgBox "Timer already On !", vbInformation
End If
End Sub