[Excel VBA] How to Set UserForm Position (Center / Manual)

Introduction

When using UserForms created in VBA with default settings, they may appear in unexpected locations. To improve tool usability, it is important to display the form in an easy-to-understand position, such as the center of the screen or near the Excel window.

In VBA, you can easily control the initial display position by changing the UserForm’s StartUpPosition property. This article explains the settings for StartUpPosition and how to precisely specify the position in pixels.

Automatic Setting with StartUpPosition Property

The StartUpPosition property is the easiest way to determine where the form appears. You can change this setting in the UserForm’s Properties window or directly in the VBA code.

Value	Constant	Description
0	vbStartUpManual	Manual. You specify the display position using the .Top and .Left properties. (Explained later)
1	vbStartUpOwner	Center of Owner. Displays in the center of the Excel window (Parent form). (Default)
2	vbStartUpScreen	Center of Screen. Displays in the center of the entire PC monitor screen.
3	vbStartUpWindowsDefault	Windows Default. Displays at a position determined by Windows.

Sample Code

The following macro displays a UserForm named MainForm using different StartUpPosition settings.

' --- 1. Show in the center of the Excel window ---
Sub ShowFormAtCenterOwner()
    With MainForm
        .StartUpPosition = 1 ' Center of Owner (vbStartUpOwner)
        .Show
    End With
End Sub

' --- 2. Show in the center of the entire screen ---
Sub ShowFormAtCenterScreen()
    With MainForm
        .StartUpPosition = 2 ' Center of Screen (vbStartUpScreen)
        .Show
    End With
End Sub

Usually, setting 2 (vbStartUpScreen) is recommended as it displays the form in the center of the screen, which is the most intuitive position for users.

Manually Setting the Position (StartUpPosition = 0)

If you set StartUpPosition to 0 (vbStartUpManual), you can freely decide the display position in pixels using the form’s .Top (vertical position) and .Left (horizontal position) properties.

Sample Code

The following macro displays MainForm at a position slightly offset from the top-left of the Excel window (100 pixels down, 200 pixels right).

' Manually specify the form display position
Sub ShowFormAtManualPosition()

    With MainForm
        ' First, set to manual mode
        .StartUpPosition = 0 ' Manual (vbStartUpManual)
        
        ' Specify position in pixels
        .Top = Application.Top + 100  ' 100 pixels down from the top of the Excel window
        .Left = Application.Left + 200 ' 200 pixels right from the left of the Excel window
        
        ' Show the form
        .Show
    End With

End Sub

Code Explanation

• .Top = Application.Top + 100: Sets the top position of the form (.Top) to 100 pixels below the top edge of the Excel application window (Application.Top).
• .Left = Application.Left + 200: Similarly, sets the left position of the form (.Left) to 200 pixels to the right of the left edge of the Excel window (Application.Left).

Using this method allows you to display the form near the Excel window as intended, even in a multi-monitor environment.

Summary

In this article, I introduced methods for controlling the display position of UserForms.

• Basically, use the StartUpPosition property. Setting 2 (Center of Screen) is particularly convenient.
• If you want to display it in a specific arbitrary position, set StartUpPosition to 0 (Manual) and specify the coordinates using the .Top and .Left properties.

The position of the form is a key factor directly linked to the usability of the tool. Please use this technique to display your forms in the optimal position so users can operate them intuitively.