Excel VBA: Show a UserForm When the Workbook Opens

To show an Excel VBA UserForm when a workbook opens, put a Workbook_Open event procedure in the ThisWorkbook code module and call the form’s Show method. Save the file as a macro-enabled workbook, such as .xlsm, and make sure Excel allows its macros to run.

How the workbook-open event works

Workbook_Open is an event that Excel raises when a workbook opens. Its procedure belongs to that workbook’s ThisWorkbook object, not to a regular VBA module, a worksheet module, or the UserForm itself. When the event fires, its code can initialize information, display a form, or perform other startup tasks.

A UserForm is a custom dialog or interface created in the Visual Basic Editor (VBE). It might welcome users, collect a date or selection, provide navigation buttons, or present instructions before they work with the sheets. Calling Show displays the form; it does not, by itself, make the workbook’s macros trusted or guarantee that the form will appear in every Excel environment.

Create the UserForm and add the event code

Open the workbook in Excel for Microsoft 365 on Windows. Press Alt+F11 to open the VBE. On a Mac, use Option+F11; depending on the keyboard, you may also need to hold Fn.
In the VBE, choose Insert > UserForm. If the Project Explorer is not visible, use View > Project Explorer. Select the new form and use the Properties window to change its (Name) and Caption if desired.
In Project Explorer, find the workbook’s project and double-click ThisWorkbook. Do not paste the event procedure into Module1 or a worksheet’s code window.
Paste the following example into the ThisWorkbook code window. It creates an instance of a form named UserForm1 and displays it modally, which means the user must dismiss the form before returning to the workbook.

Private Sub Workbook_Open()
    Dim startupForm As UserForm1

    ' Create and display the startup form.
    Set startupForm = New UserForm1
    startupForm.Show vbModal

    ' Release this instance after the modal form closes.
    Set startupForm = Nothing
End Sub

If you renamed the form, replace UserForm1 in the declaration and the New expression with its actual name. A shorter alternative is UserForm1.Show, which displays the form’s default instance. The explicit instance shown above makes it clear which form object the event creates.

Return to Excel and use File > Save As. Choose Excel Macro-Enabled Workbook (*.xlsm). The regular .xlsx format cannot store VBA project code; saving a macro-containing workbook in that format removes the macros.
Close the workbook and open it again to test the startup behavior. If Excel displays a security warning, only enable macros when you trust the file and its source.

What happens when the form is shown

With vbModal, the code pauses at the Show line until the form is hidden or unloaded. This is often appropriate for a welcome screen or required choice: the user deals with the prompt before using the workbook. A modal form can prevent interaction with the workbook window while it is open, so avoid making it a dead end. Provide a clear way to continue, cancel, or close it.

You can instead call startupForm.Show vbModeless to let the user interact with the form and workbook at the same time. A modeless form is useful as a floating control panel, but it needs more careful lifetime management. If the form should remain open after the event procedure finishes, keep a reference to its instance in a module-level variable or use the form’s default instance; do not assume a local variable will keep every modeless instance available indefinitely.

Closing and hiding are different. Unloading a form removes that instance and its controls from memory; hiding it leaves the instance loaded, so its values may remain available. A form’s close button normally unloads it. If a button on the form should close it, its click procedure can use Unload Me. Decide whether values need to be read after the user closes the form before choosing whether to unload or hide it.

Prepare the form for a useful startup

A form that opens successfully can still be confusing if it has no clear purpose or exit. Before distributing the workbook, give the form a descriptive caption, explain what the user should do, and test its controls. For example, a data-entry workbook might use a startup form to ask which reporting period to load, then pass that choice to a routine that prepares the relevant worksheet.

Startup code can also set form properties or load values before showing it. Keep the event procedure focused: call a separate setup routine if initialization becomes substantial, and validate any user input before using it. For example, a form that requests a worksheet name should handle a missing or misspelled sheet rather than assuming the requested sheet exists.

Consider whether the form should appear every time the workbook opens. If users need to dismiss a welcome screen only once per work session, a startup form may be suitable. If the form is an optional tool, a button on a worksheet or a command on the ribbon may be less intrusive. A form that appears on every open should not obscure important information or repeatedly ask for a choice the workbook already knows.

Common reasons the form does not appear

The procedure is in the wrong module. The event must be in the specific workbook’s ThisWorkbook module. A procedure in a standard module with the same name is not the workbook event.
Macros are disabled. Excel does not run VBA when macros are blocked. Check the security message and your organization’s policy. Do not lower macro security globally to make one file work.
The workbook was saved as .xlsx. Reopen the macro-enabled source or restore the code from a backup, then save as .xlsm or another macro-capable format.
The form name does not match. The name used in the code must be the form’s VBA name, shown as (Name) in Properties; the visible caption is not necessarily that name.
Events are disabled. If other code has set Application.EnableEvents to False, workbook events may not fire. Re-enable events in the Immediate window with Application.EnableEvents = True, or correct the code that disabled them. Avoid changing this setting casually in a shared Excel session.
Another error interrupts startup. A compile error, missing reference, or error in startup code can stop execution. In the VBE, use Debug > Compile VBAProject to find compile problems, then test the form separately.

To diagnose the event, put a breakpoint on its first executable line by clicking beside that line in the VBE, then reopen the workbook with macros enabled. If execution stops there, the event is firing and the problem is likely in the form name, construction, or later code. Remove the breakpoint after troubleshooting. For a controlled test, close and reopen the workbook rather than assuming that running a macro manually is identical to an open event.

Security, compatibility, and testing

VBA is executable code, so an Excel security warning is an important safeguard, not just an inconvenience. Only enable macros for a workbook from a source you trust. Organizations may block internet-downloaded macros or enforce other policies; follow the organization’s approved process rather than trying to bypass it. If a workbook is distributed to colleagues, explain what the macro does and provide it through an approved, trusted location where applicable.

Excel for the web does not run VBA macros or display VBA UserForms. Users must open the workbook in a desktop version of Excel for the event-driven form to work. Excel for Mac supports VBA, but its menus, keyboard behavior, and some form controls or API-dependent code can differ from Windows. If Mac users matter, test the workbook on the relevant Mac version and avoid Windows-specific dependencies. A UserForm that opens on one platform should not be assumed to behave identically on another.

Before changing startup code, save a separate copy of the workbook. VBA actions cannot be undone with Ctrl+Z (or Command+Z on Mac), and an event can run automatically when a file opens. Test in a copy with noncritical data, verify both the normal close path and any cancel path, then reopen the saved file to confirm the actual startup sequence.

Alternatives to opening a form automatically

Automatic display is not always the best interface. A worksheet-based instruction panel is visible without relying on macros and works in Excel for the web. A shape or form control assigned to a macro can let desktop users open a form when they need it, though the macro still requires a macro-capable file and permission to run. A task pane or other add-in interface may suit a broader deployment, but it involves a different development and distribution approach.

Another option is the older Auto_Open macro in a standard module. For a workbook-specific startup action, Workbook_Open is generally the clearer choice because it is an event procedure attached to the workbook object. Avoid implementing both mechanisms for the same display without a deliberate reason: otherwise the form may be shown twice or startup behavior may become difficult to trace.

Frequently Asked Questions

Does Workbook_Open run automatically? Yes, when the workbook opens in desktop Excel with macros permitted and application events enabled. It does not run in Excel for the web, and manually running a similarly named procedure is not the same as opening the workbook.

Can I put Workbook_Open in Module1? No. The event procedure belongs in the workbook’s ThisWorkbook module. A regular module can contain helper procedures that the event calls, but it does not receive this workbook event just because a procedure has that name.

Why does the form appear when I run the code but not when I reopen the file? Check that the event is in ThisWorkbook, macros are allowed, the file is saved as .xlsm, and events have not been disabled. Also confirm that the saved workbook is the one containing the code.

Can the UserForm open modelessly? Yes. Use Show vbModeless instead of Show vbModal. A modeless form allows workbook interaction while open, but its instance must remain available for as long as the form is needed.

Will the UserForm work in Excel for the web? No. Excel for the web does not run VBA or show VBA UserForms. Open the macro-enabled workbook in desktop Excel, or redesign the interaction with a web-compatible worksheet or other supported 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.