Excel VBA: Center a UserForm on Screen Automatically

To center an Excel VBA UserForm on the screen, set its StartUpPosition property to 2 - CenterScreen before displaying it. You can set that property in the UserForm’s Properties window or in VBA immediately before calling Show; for a form centered over Excel instead, use 1 - CenterOwner.

What StartUpPosition controls

StartUpPosition tells the Microsoft Forms UserForm where to appear when it opens. It is a positioning choice, not a command that changes the form’s size or the location of its controls. For the usual case—opening a dialog in the middle of the screen—choose 2 - CenterScreen.

The property has four standard values:

0 - Manual: Use the UserForm’s Left and Top settings to determine its position.
1 - CenterOwner: Center the form relative to its owner window, usually the Excel window that opened it.
2 - CenterScreen: Center the form on the screen.
3 - Windows Default: Let Windows choose the position.

These values are the built-in options for the MSForms UserForm property. If the form should stay near the workbook or Excel window, CenterOwner is often more useful than CenterScreen. If the form should appear in a precisely chosen location, use Manual and assign position values yourself.

Set the property in the UserForm designer

For a form that should always open centered, setting the property in the designer is the simplest approach. In Excel for Microsoft 365 on Windows, open the workbook, press Alt+F11 to open the Visual Basic Editor, and select the UserForm in the Project Explorer. If the Properties window is hidden, press F4. Find StartUpPosition and select 2 - CenterScreen from its drop-down list.

Save the workbook as a macro-enabled Excel workbook (.xlsm) if it contains VBA, then show the form as usual. The setting belongs to that form, so you do not need to repeat a positioning command every time the form opens. This is a good choice when one form has one consistent positioning rule.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. You can then open the editor with Developer > Visual Basic. In Excel for Mac, VBA is available, but keyboard shortcuts and some editor details differ; depending on the keyboard, opening the editor may require Option+F11 or the Fn key as well. Excel for the web does not run VBA macros or display VBA UserForms.

Set CenterScreen in VBA before Show

You can also choose the position in the procedure that opens the form. Setting it immediately before Show makes the intended behavior clear and is useful when the same form must be positioned differently in different situations. The example creates a form instance, centers it on screen, displays it modally, and unloads it after the user closes it.

Public Sub ShowCenteredForm()
    Dim frm As UserForm1

    ' Create an instance of the UserForm.
    Set frm = New UserForm1

    ' Center the form on the screen before displaying it.
    frm.StartUpPosition = 2
    frm.Show vbModal

    ' Release the form after it closes.
    Unload frm
    Set frm = Nothing
End Sub

Replace UserForm1 with the actual name shown in the Project Explorer. The order matters: assign StartUpPosition before Show, because the position is applied as the form is displayed. A modal form pauses the calling procedure until the form is hidden or unloaded; that is why the cleanup lines run after the user dismisses it. If you use a modeless form, its lifetime and cleanup need a different arrangement because the opening procedure continues while the form remains visible.

Before running any macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z; even when a particular macro only opens a dialog, keeping a backup is a sensible precaution when editing or testing code.

Center on the screen or center over Excel?

CenterScreen and CenterOwner solve different layout problems. CenterScreen aims to place the UserForm in the middle of the screen. CenterOwner places it relative to the window that owns or opened the form. If Excel is maximized and the dialog is intended to feel attached to that Excel session, CenterOwner can be the more natural choice. If Excel is not maximized, the form may appear over the Excel window rather than in the physical center of the display.

On a computer with multiple monitors, “screen” can be ambiguous in practice: the monitor containing Excel, the primary monitor, or another display may be relevant to a user’s setup. Positioning behavior can depend on Windows, Office, and the form’s owner. If the exact monitor matters, test the form on the actual multi-monitor arrangement rather than assuming the centered position will match every configuration. For many ordinary single-monitor workbooks, CenterScreen is sufficient.

Do not confuse centering a form with centering a worksheet selection or cell contents. The UserForm property affects the dialog window only; it does not change worksheet alignment, zoom, or the active cell.

When Manual positioning makes sense

Choose Manual when the form must appear at a specific location, such as beside a chart or near a particular part of a workflow. With Manual selected, set the form’s Left and Top properties before showing it. Those coordinates describe the form’s position; they are not worksheet row and column numbers. A fixed coordinate can be appropriate for a controlled kiosk-style setup, but it is less dependable across different display sizes, scaling settings, and monitor arrangements.

Another practical reason to use Manual is to preserve a position chosen by the user. That requires storing the previous position and checking that it remains on a visible part of the current display when the form next opens. Otherwise, a user who changes monitors or display resolution could end up with a form partly or completely off-screen. For a simple data-entry dialog, using a built-in centering option avoids that extra state-management work.

Common positioning problems and fixes

The form still opens in the wrong place: Check that you changed the property on the intended UserForm, not a control on it or another form. If setting it in code, confirm that the assignment runs before Show.
The form opens centered only sometimes: Look for other code that changes StartUpPosition, Left, or Top. Also check whether a different form instance is being created or shown.
The form appears behind another window: That is a window-activation or ownership issue, not necessarily a centering failure. CenterScreen does not promise that a form will always be the frontmost window in every desktop situation.
The UserForm is partly off-screen: This is more likely with Manual positioning or after display changes. Try CenterScreen or CenterOwner, and check the workbook on the target monitor configuration.
The form looks too large or small: StartUpPosition does not control size. Adjust the UserForm’s dimensions and control layout separately, and account for Windows display scaling when checking the result.

Also distinguish Hide from Unload. Hiding a form leaves that instance loaded, so showing the same instance again can retain its state and may not behave like a fresh launch. Unloading removes the instance; a newly created instance starts again using its configured positioning behavior. That distinction can explain why a form seems to remember earlier settings.

Windows, Mac, and Excel for the web

The examples here target Excel for Microsoft 365 on Windows, where VBA UserForms are commonly managed in the Visual Basic Editor. Excel for Mac supports VBA, but UserForm behavior and editor capabilities are not identical across operating systems and releases. If a workbook must run on both platforms, check its forms on each intended version; do not assume that a Windows positioning result will be pixel-for-pixel identical on a Mac or across different display setups.

Excel for the web cannot run VBA, so a workbook’s macros and UserForms cannot be used there. A workbook may still open in the browser, but users who need the form must open it in a desktop version of Excel that supports the workbook’s VBA. Macro security also matters: enable macros only for workbooks from a source you trust, and do not lower Excel’s global security settings just to make a form appear.

Choosing the right positioning option

For most forms, the choice can be made by asking where the user expects the dialog to appear. Use CenterScreen for a form that should be centered on the display, CenterOwner for one that belongs visually to the Excel window, and Manual only when a defined location or saved position is genuinely needed. Windows Default is available when you prefer to leave placement to Windows, but it does not express the same explicit centered behavior.

A useful rule is to start with the designer property and use code only when the position needs to vary. That keeps a simple form easy to maintain while allowing a more complex workflow to set different behavior deliberately. Whichever option you choose, test the form at the size and display arrangement your users are likely to have.

Frequently Asked Questions

What value centers an Excel VBA UserForm on the screen?
Set StartUpPosition to 2, which means CenterScreen. Set it in the Properties window or in VBA before calling Show.

How do I center a UserForm over the Excel window?
Use StartUpPosition = 1, or select 1 - CenterOwner in the Properties window. This centers relative to the owning window rather than asking for the center of the screen.

Can I center a UserForm from its Initialize event?
You can configure form properties in initialization code, but setting StartUpPosition in the procedure that opens the form, before Show, makes the timing explicit. The designer property is also suitable when the form always uses the same position.

Why does my UserForm open off-screen?
Check whether its StartupPosition is Manual or whether code assigns fixed Left and Top values. A saved position can become invalid after changing monitors or display settings; use a centering option or validate saved coordinates before showing the form.

Does centering a UserForm work in Excel for the web?
No. Excel for the web does not run VBA macros or show VBA UserForms. Open the workbook in a compatible desktop Excel application to use that interface.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.