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

by Allen Wyatt
(last updated January 14, 2017)

2

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, and 2016.

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

Finding and Replacing with Subscripts

Want to use Find and Replace to change the formatting of a cell's contents? You would be out of luck; Excel won't let you do ...

Discover More

Pulling a Phone Number with a Known First and Last Name

When using an Excel worksheet to store data (such as names and phone numbers), you may need a way to easily look up a phone ...

Discover More

AutoFilling with the Alphabet

If you need to fill a number of cells with a specific sequence of characters (such as the alphabet), there are several ways ...

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)

Finding Positions of Formatted Characters in a Cell

With a little bit of work, Excel allows you to format individual characters of the text you place in a cell. If you want to ...

Discover More

Deleting a File in a Macro

Macros give you a great deal of control over creating, finding, renaming, and deleting files. This tip focuses on this last ...

Discover More

Deleting Zero Values from a Data Table

Want to get rid of all the zero values in a range of cells? This tip provides a couple of different ways you can accomplish ...

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 for this tip:

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 seven minus 6?

2017-01-15 12:50:16

Louis LAFRUIT

Allen,
My warmest thanks. I really appreciate.
Louis


2017-01-14 15:31:42

Gian

I have tested on which versions?
With Excel 2016 I do not get the expected result

New code:

Option Explicit

Private Sub UserForm_Initialize()
Me.StartUpPosition = 0
Me.Top = Application.Height + Application.Top - ActiveWindow.UsableHeight
Me.Left = Application.Left
End Sub


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.

Links and Sharing
Share