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.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
If you have a macro that selects different columns in a worksheet while processing information, you may get some undesired ...
Discover MoreIf you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in a ...
Discover MoreThe colors used in a Excel are not as simple as they used to be. Here are some ideas relative to working with those colors in ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-01-15 12:50:16
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments