How to get X Y screen coordinates of Excel cell/Range

[pociners]

I was wondering if there's a way to retrieve back the Point (x,y) of the top left cell of a selected range?

I'm talking about the absolute screen coordinates, not the column/row, i.e. it should return a Point object (or two ints specifying the x and y coordinates), not a range or anything like that.

thanks

 

[JoeMo]

Maybe:

Sub XYCoordinates()
With Selection
    X = .Cells(1, 1).Left
    Y = .Cells(1, 1).Top
    MsgBox "X = " & X & vbNewLine & "Y = " & Y
End With
End Sub

 

[pociners]

Maybe:

Sub XYCoordinates()
With Selection
    X = .Cells(1, 1).Left
    Y = .Cells(1, 1).Top
    MsgBox "X = " & X & vbNewLine & "Y = " & Y
End With
End Sub

thanks for your advise,
i've tried that code, but if i scroll down/right the window, it'll return the wrong coordinates in screen.

can you give me another codes maybe? :D
thanks..

 

[JoeMo]

thanks for your advise,
i've tried that code, but if i scroll down/right the window, it'll return the wrong coordinates in screen.

can you give me another codes maybe? :D
thanks..

When you scroll are you changing the selected range? Can you give me an example of a range you selected and the values of the "wrong" coordinates that the code returns?

 

[pociners]

When you scroll are you changing the selected range? Can you give me an example of a range you selected and the values of the "wrong" coordinates that the code returns?

I've found the answers from another forum and i modified the code by myself

Option Explicit
Private Type RECT
  Left                  As Long
  Top                   As Long
  Right                 As Long
  Bottom                As Long
End Type

Private Declare Function GetDC Lib "user32" ( _
  ByVal hwnd As Long) As Long
Private Declare Function ReleaseDC Lib "user32" ( _
  ByVal hwnd As Long, ByVal hDC As Long) As Long
Private Declare Function GetSystemMetrics Lib "user32.dll" ( _
  ByVal nIndex As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" ( _
  ByVal hDC As Long, ByVal nIndex As Long) As Long
Private Declare Function GetCurrentThreadId Lib "kernel32" ( _
  ) As Long

Private Type POINTAPI
    x As Long
    y As Long
End Type
Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
Private Function ScreenDPI(bVert As Boolean) As Long
  'in most cases this simply returns 96
  Static lDPI&(1), lDC&
  If lDPI(0) = 0 Then
    lDC = GetDC(0)
    lDPI(0) = GetDeviceCaps(lDC, 88&)    'horz
    lDPI(1) = GetDeviceCaps(lDC, 90&)    'vert
    lDC = ReleaseDC(0, lDC)
  End If
  ScreenDPI = lDPI(Abs(bVert))
End Function

Private Function PTtoPX(Points As Single, bVert As Boolean) As Long
  PTtoPX = Points * ScreenDPI(bVert) / 72
End Function

Sub GetRangeRect(ByVal rng As Range, ByRef rc As RECT)
  Dim wnd               As Window
  
  'requires additional code to verify the range is visible
  'etc.
  
  Set wnd = rng.Parent.Parent.Windows(1)
  With rng
    rc.Left = PTtoPX(.Left * wnd.Zoom / 100, 0) _
              + wnd.PointsToScreenPixelsX(0)
    rc.Top = PTtoPX(.Top * wnd.Zoom / 100, 1) _
             + wnd.PointsToScreenPixelsY(0)
    rc.Right = PTtoPX(.Width * wnd.Zoom / 100, 0) _
               + rc.Left
    rc.Bottom = PTtoPX(.Height * wnd.Zoom / 100, 1) _
                + rc.Top
  End With
End Sub

Sub GetCoordinateXY()
Dim rc As RECT
On Error GoTo done
Call GetRangeRect(ActiveCell, rc)
X = rc.Left
Y = rc.Top
done:
End Sub

 

[OldManExcellor]

Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long

is colored red in my VBA IDE. What could be wrong? I have x64 windows and Excel 2013 if that is relevant.

 

[ebleyebley]

Private Declare Function GetDC Lib "user32" ( _
ByVal hwnd As Long) As Long

is colored red in my VBA IDE. What could be wrong? I have x64 windows and Excel 2013 if that is relevant.

Put Private PtrSafe instead of Private. This is a 64bit thing. A late answer, but in case anyone else needs this info.