|
|
|
|
|
|
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
|
Option Explicit
Sub ShowForm()
UserForm1.Show False
End Sub
Option Explicit
Private Sub UserForm_Activate()
With UserForm1
.Top = Application.Top + 125
.Left = Application.Left + 25
End With
End Sub
Option Explicit
Private Sub UserForm_Activate()
With UserForm1
.Top = 125
.Left = 25
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.
|
|