×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Screen coordinates of Inside Top Left Corner of excel chart by vba

Screen coordinates of Inside Top Left Corner of excel chart by vba

Screen coordinates of Inside Top Left Corner of excel chart by vba

(OP)

I am trying to look for a vba code which can find the screen coordinates of the inside top left corner of the chart in excel. Please help me out.

RE: Screen coordinates of Inside Top Left Corner of excel chart by vba

Hi,

You have two Area Objects to choose from the...

ChartArea Object
PlotArea Object

They both have Top and Left Properties, but the PlotArea Object also has InsideTop and InsideLeft Properties.

https://docs.microsoft.com/en-us/office/vba/api/ex...)

BTW, if none of those are what you need, there's the ChartObject Object, that has Top and Left Properties. The ChartObject Object is how a chart on a sheet is defined as opposed to a chart sheet which is a Chart Object and as such has no Top or Left Properties.

FAQ707-4811: Charts & VBA

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Screen coordinates of Inside Top Left Corner of excel chart by vba

But none of those are the screen coordinates ...

RE: Screen coordinates of Inside Top Left Corner of excel chart by vba

Quote:

PlotArea.Top property (Excel)
05/09/2019

Syntax
Returns or sets a Double value that represents the distance, in points, from the top edge of the object to the top of row 1 (on a worksheet) or the top of the chart area (on a chart).
https://docs.microsoft.com/en-us/office/vba/api/ex...

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Screen coordinates of Inside Top Left Corner of excel chart by vba

That's still client coordinates, not screen coordinates, so there is additional calculation to do ...

RE: Screen coordinates of Inside Top Left Corner of excel chart by vba

I had a quiet moment, so, if the OP really was asking for absolute screen coordinates rather than client coordinates:

CODE

Option Explicit

Private Declare Function Rectangle Lib "gdi32" (ByVal hdc As Long, ByVal X1 As Long, ByVal Y1 As Long, ByVal X2 As Long, ByVal Y2 As Long) As Long
Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long

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


Public Sub Main()
    Dim myRect As RECT
    myRect = GetShapeScreenRect(ActiveSheet.Shapes("Chart 2")) ' we now have bounding rectangle of the Shape ("chart 2" in this case) in absolute screen coordinates
    'Rectangle GetDC(0), myRect.Left, myRect.Top, myRect.Right, myRect.Bottom ' you will only see this rectangle briefly if at all if sheet has the focus since Excel will repaint the application window
End Sub


Private Function GetShapeScreenRect(ByVal myShape As Shape) As RECT
  Dim myWind As Window
  
  Set myWind = Application.Windows(1) ' Use Application window for origin offset, and for Zoom factor (which affects client coordinates used by Excel)
  With myShape
    GetShapeScreenRect.Left = PointsToPixels(.Left * myWind.Zoom / 100) + myWind.PointsToScreenPixelsX(0)
    GetShapeScreenRect.Top = PointsToPixels(.Top * myWind.Zoom / 100) + myWind.PointsToScreenPixelsY(0)
    GetShapeScreenRect.Right = PointsToPixels(.Width * myWind.Zoom / 100) + GetShapeScreenRect.Left
    GetShapeScreenRect.Bottom = PointsToPixels(.Height * myWind.Zoom / 100) + GetShapeScreenRect.Top
  End With
End Function

Private Function PointsToPixels(Points As Single) As Long
  PointsToPixels = Points * 96 / 72 ' 96 = magic number. In reality we should query system for actual pixels per (logical) inch. And should cater for the fact that this value may differ between horizontal and vertical
End Function 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close