Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro
Here is how you can trigger a macro by mousing over a range of cells.
This is accomplished without any assistance whatsoever from ActiveX controls or any embedded objects. The cells being moused over (not selected, double-clicked, right-clicked, or changed) are truly naked worksheet cells that you can do anything else with as usual.
To make this happen, Applications Programming Interface (API, the Windows program language) is utilized to identify the X and Y coordinates (distance from left and distance from top of the window) of where the mouse is as it hovers over whichever cell(s) you specify.
In the next two pictures, relating to the programming code below and marked as Step 1 and Step 2, see that the upper left coordinates and the lower right coordinates are identified for range B3:D3. You do this by running the macro named
PositionXY twice – – once for each set of upper left and lower right paired coordinates.
For example, when you see your target range’s upper left coordinates, double-click a cell to stop the macro, and make a note the coordinates that are in cell G1. Repeat the process for the lower right coordinates of your desired range. These directions may seem complicated as you read them, but they are actually very simple housekeeping tasks which only take a few moments to complete.
Public Declare Function GetCursorPos Lib "user32" _
(lpPoint As POINTAPI) As Long
Public Type POINTAPI
x As Long
y As Long
Dim lngCurPos As POINTAPI
'Temporary code line to visually identify the X and Y coordinates.
'It will be replaced by the real line of code to run a macro
'when the coordinates are met.
Range("G1").Value = "X: " & lngCurPos.x & " Y: " & lngCurPos.y
Identify the X and Y coordinates for the upper left corner of your mouseover target range.
In this example, as seen in cell G1 pointed at by the green arrow, X is 151 and Y is 159.
Finally, clear the contents of (in this example) cell G1. Then, modify the above macro by replacing the involvement of helper cell G1 with the range of coordinates that, when met, triggers the macro you want to run. In this example, a UserForm helps the car dealership manage its inventory.
Here’s the actual macro, based on the coordinates gotten from Step1 and Step 2. When you run the macro, as depicted in the below picture, the UserForm will appear when you mouse over the range of B3:D3. Note that the API code is unchanged, and stays in the module just as you see it above.
Dim lngCurPos As POINTAPI
If (lngCurPos.x >= 121 And lngCurPos.x <= 411) And _
(lngCurPos.y >= 159 And lngCurPos.y <= 223) Then
Finally, two notes:
(1) If the column width or row height changes for the cell(s) of interest, you need to modify the code to reflect those new coordinates.
(2) The macro itself in real practice might be turned on by the sheet's Activate event, and halted at the Deactivate event. This example is just to show how the mousing over cells can trigger a macro.