Excel VBA: .RangeFromPoint returns the wrong cell in multi-display setup

[cybergripe]

This is my first post so apologies in advance if I violate any standards!

I’m trying to perform an action based on the cell under the user’s mouse location, by reading the mouse location and using the .RangeFromPoint method of the active window to find the cell. The below code correctly identifies the cell in a single display setup. In a dual-display setup, it works on either display when both are set to the same scaling value in Windows (under Display Settings, “Change the size of text, apps, and other items”). However, it does not correctly identify the cell on both displays when they are set to different scaling values. For example, a laptop as main display set to 125% scaling and a monitor as secondary display set to 100% scaling. It works when the Excel window is on the laptop, but when moved to the monitor, it returns the wrong cell.

It seems that changing the Excel “dynamic scaling” option fixes it (Options\User Interface Options\When using multiple displays\Optimize for compatibility), but that change has other undesirable effects. My workbook is for hundreds of users so it’s also not feasible to have all of them change a setting.

I’m trying to make this work for as many display setups as possible. Does anyone have ideas? Thanks for your help!

Public Declare PtrSafe Function GetCursorPos Lib "USER32" (lpPoint As POINTAPI) As LongPtr

Public Type POINTAPI
    MouseX As Long
    MouseY As Long
End Type

Sub getMouseRange()

    Dim lngCurPos As Long
    Dim rngMouse As Range

    GetCursorPos lngCurPos
        
    Set rngMouse = ActiveWindow.RangeFromPoint(lngCurPos.MouseX, lngCurPos.MouseY)

End Sub

 

[Jaafar Tribak]

What happens if you multiply lngCurPos.MouseX and lngCurPos.MouseY each by 1.25 ?

Also, what happens if the opposite scenario is true. ie := Main display scale is 100% and second display sacle is 125% ?