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
End Type
Sub PositionXY()
Dim lngCurPos As POINTAPI
Do
GetCursorPos lngCurPos
'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
DoEvents
Loop
End Sub
Step 1
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.
Step 2
Identify the X and Y coordinates for the lower right corner of your mouseover target range.
In this example, as seen in cell G1 pointed at by the red arrow, X is 411 and Y is 223.
Step 3
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.
Sub PositionXY()
Dim lngCurPos As POINTAPI
Do
GetCursorPos lngCurPos
If (lngCurPos.x >= 121 And lngCurPos.x <= 411) And _
(lngCurPos.y >= 159 And lngCurPos.y <= 223) Then
UserForm1.Show
Exit Sub
End If
DoEvents
Loop
End Sub
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.
Hi
I find interesting the code found in “Toms Tutorials For Excel: Mousing Over a Cell to Run a Macro” however there is something I can’t follow….
when I move around the sheet, nothing gets triggered therefore nothing happens with the code found in Sub PositionXY()
I assume the mouse position is continuously being monitored to know its position and trigger the macro when found BUT…how does the workbook does the actual monitoring ??
I don’t know maybe I missed a step somewhere…
I hope you can help me
thanks
Thanks for following my Excel blog.
It looks like nothing is happening except when the mouse intersects with the positionx and positiony coordinates you specify that represent the rectangular area on the sheet where you want something to happen. The key piece is the POINTAPI structure as I exampled it in my code, where you specify the x and y coordinates, declaring them at the module level. From my column widths and row heights on that worksheet which I showed a picture of, I knew that range B3:D3 were defined in POINTAPI as between 121 and 411 horizontally, and between 159 and 223 vertically. The If structure only evaluates to true when the pair of coordinates is met via the mouse movements along the sheet, always being monitored behind the scenes (but shown on purpose in cell G1 in my screen shots).
Tom,
This is wonderful.
Is it possible to make the points dynamic.
What I am after is the user might use zoom or windows maximize / minimize buttons and the code will not work to make it happen.
Good question, in fact there are 2 factors involved. One is the zoom setting as you said. The other is the location of the Excel window. If the Excel window is maximized, then there is no extra consideration needed. But things do get complicated when a window is resized to show any myriad of possible coordinates depending on the myriad of possible sizes. For projects that depend on this coordinate feature (which are not many, it’s not a highly requested project scope item), one way I deal with that is to program the project’s Excel windows to open and stay maximized. There are more complex routes to take, such as programmatically examining the size of the current Excel window relative to the size of the entire screen, and further relative to the current zoom, but then as a developer you need to ask yourself if all that calculation behind the scenes on every move of the mouse is worth the resources being soent on it.
That brings us to the zoom factor you mentioned which is much easier to deal with. Just take the zoom percent you have set and build that into the calculation as a percentage of adjustment needed to get the mouse location. For example, I just had a 1440 x coordinate on a particular cell at 100% zoom. At 200% zoom it was 720, which makes total sense. You can programmatically get the zoom as a an active window property using activewindow.zoom.
The problem is when you move the workbook around the screen. The macro will not be activated due to the shift in coordinates… Is there a way to link the coordinate change to a cell value? meaning if hover over cell range with value “hover” activate macro, as opposed to hover over POINTAPI
Thanks for following my Excel blog page. Wow, good question! I’ll think about this and see what I come up with for a solution. I am already thinking about the benefits of being able to do this with other projects beyond your example, good question.
Hi Tom,
Thanks for the interesting code.
Could you provide the sample updated workbook with Joe’s suggestion?
Regards,
Don
Who is Joe? I don’t see a suggestion by anyone named Joe for this tutorial and don’t know who or what you are referring to.
Tom,
I think the question being asked in a round about way is how is the POSITIONXY Subroutine being called / Triggered?
I added the Declaration and the POINTAPI Type to a Module and added the POSITIONXY Subroutine which displays the X/Y Coordinates to the worksheet, but the POSITIONXY Subroutine never gets triggered regardless of where I move the mouse. There has to be a chunk of code missing which calls the POSITIONXY Subroutine, maybe like using the Worksheet SelectionChange Subroutine
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call PositionXY
End Sub
Hello Tom
The simple things are the most beautiful ones.
Your solution is super simple and elegant.
Suits into my needs and works perfectly.
Congratulations and thanks
Rui
Thank you, Rui!
Hi Tom,
Is it possible to convert x,y coordinates into cell range? if mouse hover over A1 it will show “A1” instead of x,y coordinates?
Thank you in advance,
This is one of the best codes regarding “hovering” that I can find.
John
Wow, that is a good question that I had not thought about until your question. This is do-able but very volatile. The issue is to know where the cell is that you want to display, relative to the edge of the window but also relative to other cells. So if a column width or row height changes, the X and Y coordinates for its location will be different. As will be the case for resizing the Excel window.
My only idea to work this is actually spoken to in the paragraph immediately preceding my posted code, where you can make note and modify the code for the X and Y coordinates for a certain cell. With many cells, that is a nightmare. And of course even with one cell, once the user changes a row or column size, that negates the foregoing notations about what cell is where on an X and Y basis. I suppose you could go to the programmatic lengths needed to make the Excel window, row heights and column widths to be not resizable, but I would not count on perfect results. The only other option is to use invisible embedded shape objects as landmarks which would be more reliable to employ but then you have a bunch of shape objects on your worksheet that makes it look sub-amateur.
I will keep thinking about this but that is the best I can come up with at my first time thinking about it. Good question!