ActiveWindow.RangeFromPoint does not work on Display with scale 150%

Jiri Pik 0 Reputation points

Steps to reproduce on a 64-bit version of MS Excel (Microsoft® Excel® for Microsoft 365 MSO (Version 2304 Build 16.0.16327.20200):

  • Create a blank workbook
  • ALT+F11 → Visual Basic Editor
  • Insert a blank module and add this code
Option Explicit

  Xcoord As Long
  Ycoord As Long
End Type

Public Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long

  1. Add this code into the ThisWorkbook
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim llCoord As POINTAPI
    Dim r As Range
    GetCursorPos llCoord
    Debug.Print "X Position: " & llCoord.Xcoord & vbNewLine & "Y Position: " & llCoord.Ycoord
    Set r = ActiveWindow.RangeFromPoint(llCoord.Xcoord, llCoord.Ycoord)

    If r Is Nothing Then
        Debug.Print r.Address
    End If
End Sub

  1. Click on a few Excel cells while your Display has scaling 100% - in the VBA Intermediate window you will see the X/Y coordinates, along with the Range address, e.g.

X Position: 5057

Y Position: 615


X Position: 5213

Y Position: 628


  1. Change your display's scaling to 150% and click on a few Excel cells - in the VBA intermediate window you will see the X/Y coordinates without the Range, e.g.

X Position: 4809

Y Position: 540

X Position: 4791

Y Position: 597

Notice, this is a bug - you should see also the Range addresses.

A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
513 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Mohammad Majed Asad 150 Reputation points
     there are a few workarounds you can try:
    Use a different method to get the selected range: Instead of using ActiveWindow.RangeFromPoint, you can use Application.Selection to get the selected range. For example, you can modify the code in the Workbook_SheetSelectionChange event as follows:
    Copy code
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
        Dim r As Range
        Set r = Application.Selection
        If Not r Is Nothing Then
            Debug.Print r.Address
        End If
    End Sub
    This should give you the selected range, regardless of the display scaling.
    Change the display scaling: Another workaround is to change the display scaling back to 100% while working in Excel. This should allow ActiveWindow.RangeFromPoint to work properly.
    I hope this helps!