On Mouse Over - run a Macro

acat · Nov 8th 2019

I have done something similar in the past, but I think I had to place some transparent shapes for mouse to cross over and that was being detected triggering a macro (scroll I think)

Wondering if there's an easier way where you could just specify a cell range and tell it if mouse moves over that range run a macro

What are your experiences with that?

**Kenneth Hobson** · Nov 9th 2019

Cursor position on the screen can change for a range. The usual method would be to determine the locations for window maximized in the Test sub. If window is not maximized, or zoomed, the coordinates will be wrong. Change coordinates to suit your window.

In a Module:

Code

'http://www.atlaspm.com/toms-tutorials-for-excel/tomstutorialsforexcel-mousing-over-a-cell-to-run-a-macro/
Public Declare Function GetCursorPos Lib "user32" _
    (lpPoint As POINTAPI) As Long
    Public Type POINTAPI
    x As Long
    y As Long
End Type


Sub IWCoords()
    Dim lngCurPos As POINTAPI, x As Double, y As Double
    '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
        Debug.Print Selection.Address, lngCurPos.x, lngCurPos.y
        DoEvents
   ' Loop
End Sub


Sub PositionXY()
    Dim lngCurPos As POINTAPI
    Do
        GetCursorPos lngCurPos
        '$A$3       69  372
        '$E$7       417           477
        If (lngCurPos.x >= 69 And lngCurPos.x <= 417) And _
                (lngCurPos.y >= 372 And lngCurPos.y <= 477) Then
                Range("A3:E7").Interior.Color = vbYellow
            'Exit Sub
            Else
            Range("A3:E7").Interior.Color = xlNone
        End If
        DoEvents
    Loop
End Sub

Display More

Right click the sheet tab, View Code, and paste:

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    PositionXY
    'IWCoords
End Sub

acat · Nov 9th 2019

I've seen that post before my question, but thanks - a bit iffy with the caveats... For now I went with my tried method I noted, but am also looking in a direction of being able to right click in a range of cells and have a list show up with preset string options, then upon selecting one for a Comment to be inserted into that cell with chosen string

On Mouse Over - run a Macro

Participate now!

Share