Calculating the Distance between the Top of the Window and Row 1

Written by Allen Wyatt (last updated January 7, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


3

When he creates a UserForm, Louis sets the Top property of the UserForm to 222, which he determined by trial and error is the pixel distance between the upper border of the active window and the top of the first row of data in the worksheet. He wonders if there is a way to calculate this distance programmatically, given that the distance can vary depending on how much of the ribbon is displayed and the height of the Formula bar.

If you need to get the height of the ribbon area, you can examine the Height property of the CommandBars("Ribbon") object, in this manner:

iHt = CommandBars("Ribbon").Height

That, of course, will give you only part of the information you ultimately need. The positioning information for a UserForm is based on the upper-left corner of the program window. Thus, you need to take into account the border thickness of the window (if there is a border), the ribbon height (mentioned above, but only if running on a version of Excel that uses the ribbon), the height of the Formula bar, any space allowed for the ruler, and so forth.

Most of these things don't have Height properties you can check, so positioning a UserForm can be a process of trial and error in order. Once you get the UserForm positioned correctly on your system, there is no guarantee it will be positioned correctly if displayed on someone else's system.

The best solution we've found is to (in this case) not reinvent the wheel. Chip Pearson, on his website, has created what he calls a "form positioner" that takes the guesswork out of positioning a UserForm. You can find information on it here:

http://www.cpearson.com/Excel/FormPosition.htm

There is no charge; it is free. It allows you to position a UserForm relative to any cell on the screen. If you develop macros that rely on UserForms, you'll want to check out what Chip has to offer.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2309) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Setting the Calculation Default

Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...

Discover More

Creating a Table of Authorities

In legal documents a table of authorities is a common element. Creating the table is easy to do if you apply the ...

Discover More

Selectively Changing Endnotes to Footnotes

Want to turn some (but not all) of your endnotes into footnotes. Rather than do it manually, you can apply the shortcut ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Preserving the Undo List

The undo list can be a lifesaver when working in a macro. Unfortunately, the undo list is not preserved when you run a ...

Discover More

Executing a Macro Every 15 Minutes

Need to run a macro at a given interval? It's easy to do when you learn how to use the .OnTime method, described in this tip.

Discover More

Stopping Excel from Deleting Macros from a Workbook

When working with very large workbooks, it is possible for Excel to behave erratically. This tip looks at ways you can ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is two more than 9?

2024-01-18 10:07:28

J. Woolley

@Malcolm Sokol
Please review my comment immediately below yours.


2024-01-17 22:10:31

Malcolm Sokol

I downloaded Chip Pearson' userform positioner and have a few observations. It is highly dependent on the mode of the Excel Window. The behavior of the positioning is different when the window is Full vs. normal mode. In the latter instance, if I position a window and resize it even to a very small part of the desktop on which it is open, the form position varies all over the place even though his for displays the same values for the positions he uses. Not sure this will allow an absolute certainty of position. In a recent application I just had to offset the command bar width to the cell top position to get the location as you noted and go with that.


2023-01-09 10:55:57

J. Woolley

My Excel Toolbox includes the following function to determine the screen coordinates of the top-left corner of the first cell in a range:
Public Function Range_ScreenXY(rTarget As Range, _
    Optional sUnits As String = "pixels") As Long()
This function returns a base-0 Long array XY() with 3 elements:
XY(0) = rTarget's original ActiveWindow.Zoom before adjustment (see below)
XY(1) = screen X-coordinate for rTarget.Left
XY(2) = screen Y-coordinate for rTarget.Top
Optional sUnits specifies the units of returned screen coordinates; only the first two characters apply and alphabetic case is ignored. The following sUnits are recognized: "pixels" (default) or "pi" or "px", "points" or "po" or "pt", "twips" or "tw". Pixels are screen pixels, not Windows' pixels, which might differ; see the DisplayScreenMetrics macro.
If necessary, ActiveWindow.Zoom will be adjusted to the nearest multiple of 5 (like 75, 80, 85...); otherwise, results are close but randomly unreliable. Results are best if Zoom > 65. If necessary, ActiveWindow will be scrolled southeast (SE) to make room for a dialog box at X-Y. This function accommodates Split with or without FreezePanes (unlike Pearson's, which might not handle Zoom either).
Note: UserForm position is based on the top-left corner of the screen (not the program window) in points (not pixels).
Note: Microsoft documentation is incorrect for PointsToScreenPixelsX/Y methods, which are reliable only when their argument is zero; see http://microsoft.wmlcloud.com/forums/t/120412.aspx.
Here is an example macro that uses Range_ScreenXY to position a UserForm:

Sub PositionUF()
    Dim UF As UserForm1, rSele As Range, sAddr As String
    Dim XY() As Long, nZoom As Long
    Const Msg = "Using the mouse, select a cell to position the UserForm." _
        & vbNewLine & "Click OK to continue or Cancel to quit."
    Const Title = "PositionUF"
    Const AdjustX = 6 'determine empirically for your configuration
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
    nZoom = ActiveWindow.Zoom
    Set UF = New UserForm1
    UF.StartupPosition = 0
    Set rSele = Selection
    Do While True
        sAddr = rSele.Cells(1).Address
        Set rSele = Nothing
        On Error Resume Next
            Set rSele = Application.InputBox(Msg, Title, sAddr, Type:=8)
        On Error GoTo 0
        If rSele Is Nothing Then Exit Do 'user clicked Cancel
        XY = Range_ScreenXY(rSele, "points")
        UF.Left = XY(1) - AdjustX
        UF.Top = XY(2)
        UF.Show vbModeless
    Loop
    Unload UF
    Set UF = Nothing
    ActiveWindow.Zoom = nZoom
End Sub

See https://sites.google.com/view/MyExcelToolbox


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.