VBA Express : Excel - Fine-Tune Userform Start Position

Excel

Fine-Tune Userform Start Position

Ease of Use

Intermediate

Version tested with

2000

Submitted by:

johnske

Description:

The userform always starts up in the exact position you specify.

Discussion:

As a developer, you are sometimes faced with the situation where you want to ensure a userform always starts in a particular position that you specify so that nothing is hidden that you want shown, and nothing is shown that you want hidden. Unfortunately, the inbuilt startup positions don't allow you much choice in the matter. If you want to use something other than the four inbuilt startup positions {viz: UserForm1.StartUpPosition = 0 (or 1, 2, 3)} you can accomplish this with the few lines of code shown below. Note: The only case this code can't be tweaked to cater for is, UserForm1.StartUpPosition = 1 (Centre owner)

Code:

instructions for use

			 '<< Code for basic module >>
 
Option Explicit 
 
Sub ShowForm() 
    UserForm1.Show False '< delete "False" for Office '97
End Sub 
 
 
 
 '****************************************************
 'The 2 macros below are mutually exclusive, use one OR the other
 '****************************************************
 
 '<< Code for userform (macro 1) >>
Option Explicit 
 
 '<< Form position with respect to application window >>
Private Sub UserForm_Activate() 
    With UserForm1 
        .Top = Application.Top + 125 '< change 125 to what u want
        .Left = Application.Left + 25 '< change 25 to what u want
    End With 
End Sub 
 
 
 
 
 'OR use
 
Option Explicit 
 
 '<< Form position with respect to monitor (macro 2) >>
Private Sub UserForm_Activate() 
    With UserForm1 
        .Top = 125 '< change 125 to what u want
        .Left = 25 '< change 25 to what u want
    End With 
End Sub

How to use:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/UserForm
Right-Click UserForm1 in the Project - VBA Project pane
Select ShowCode
Copy and paste the code for either one of the two macros into this window
Select the UserForm then "Run" (up the top) and click Run Sub/UserForm

Test the code:

Download the attached zip and extract the enclosed workbook
Open the workbook and click the "Show User Form" button
Now open the VBE window (as above)
Modify the numbers for 'Top' and 'Left' and observe the differences
Move the application window to another position and try again...
Now comment out the code in the Userform and uncomment the other code
Go back to the main window and click the button
Observe the differences

Sample File:

FixFormStartPosition.zip 7.69KB

Approved by mdmackillop

This entry has been viewed 337 times.