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:

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

Test the code:

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

Sample File:

FixFormStartPosition.zip 7.69KB 

Approved by mdmackillop


This entry has been viewed 302 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express