Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro

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.

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , , ,
9 comments on “Tom’s Tutorials For Excel: Mousing Over a Cell to Run a Macro
  1. frank says:

    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

    • Tom Urtis says:

      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).

      • Subodh says:

        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.

        • Tom Urtis says:

          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.

  2. Jose says:

    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

    • Tom Urtis says:

      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.

  3. Don says:

    Hi Tom,

    Thanks for the interesting code.

    Could you provide the sample updated workbook with Joe’s suggestion?

    Regards,
    Don

  4. Scott says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

*