VBA Open UserForm: How to Show a Form in Excel

To open a VBA UserForm in Excel, call its Show method: for example, frmCustomer.Show. Replace frmCustomer with the form’s actual name; you can run that line from a macro, another form, or a button. By default, the form opens modally, so the user must close or hide it before working in the workbook.

What a VBA UserForm Is

A UserForm is a custom window created in Excel’s Visual Basic for Applications (VBA) editor. It can contain controls such as text boxes, labels, list boxes, and buttons, allowing you to collect information or present choices without asking users to work directly in cells. A form does not appear just because it exists in the VBA project: a macro or another piece of VBA code must load and show it.

In the Visual Basic Editor, a form is listed in the Project Explorer alongside modules and worksheets. Select the form and press F4 to view its Properties window. The (Name) property is its VBA name, such as frmCustomer; this is the name you use in code. The Caption property is the title displayed in the form’s window bar. Those values can differ, so check (Name) rather than guessing from the visible title.

Open a Form from a Macro

For a reusable entry point, put a public procedure in a standard module. In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > UserForm to create a form if you do not already have one, and choose Insert > Module to add a standard module. Rename the form in the Properties window, then add a procedure like this to the module:

Public Sub OpenCustomerForm()
    ' Replace frmCustomer with the UserForm's (Name) property.
    Dim frm As frmCustomer
    Set frm = New frmCustomer

    frm.Show vbModal

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

Change frmCustomer to match your form’s (Name) property. To run the procedure from the editor, click inside it and press F5, or return to Excel, press Alt+F8, select OpenCustomerForm, and choose Run. If the workbook contains macros, save it as an Excel Macro-Enabled Workbook (.xlsm) so the VBA project can be retained.

For a quick test, the shortest version is frmCustomer.Show. That uses the form’s default instance, which VBA creates automatically when needed. Creating an explicit instance, as in the example, is useful when you want to control the form’s lifetime or open more than one instance. You do not have to create a new instance for ordinary one-form workflows.

Open a Form from a Worksheet Button

A worksheet button gives users a visible way to launch the form without opening the macro list. First create a public macro such as OpenCustomerForm in a standard module. Then, in Excel for Windows, use Developer > Insert > Button (Form Control), draw the button on the worksheet, and select the macro in the Assign Macro dialog. You can right-click the button and choose Edit Text to change its label. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon.

A Form Control button is often the straightforward choice for a workbook launcher because it can be assigned directly to a macro. An ActiveX Command Button instead uses an event procedure in the worksheet’s code module; for example, its click event can call frmCustomer.Show. ActiveX controls have additional platform and security considerations, so do not assume a workbook using them will behave identically on Mac or in Excel for the web. Excel for the web does not run VBA macros; users need the desktop Excel application to run this form code.

Modal and Modeless Forms

The optional argument to Show determines whether the form blocks interaction with the rest of Excel. If omitted, the form is modal. You can make that choice explicit with vbModal, as in the earlier example. A modal form is suitable for a short task that must be completed before the user returns to the workbook, such as entering a required value.

To let users interact with the worksheet while the form remains open, show it modelessly:

Public Sub OpenDashboardForm()
    ' Show the default instance without blocking Excel.
    frmDashboard.Show vbModeless
End Sub

Modeless windows are useful for helper panels, but they need more careful design. Users can change or delete worksheet data while the form is still open, so values displayed on the form might no longer match the sheet. A modeless form also stays open after the launching macro returns. Avoid creating a short-lived local object for a modeless form unless you deliberately retain a reference to it; otherwise, managing its lifetime can be confusing. Using the default instance is a simple option for a single modeless form.

Initialize Controls and Pass Data

Use the form’s UserForm_Initialize event to set up controls when the form is first loaded. For instance, if the form contains a text box named txtName, its code module could include:

Private Sub UserForm_Initialize()
    ' These controls must exist on the form with these names.
    Me.Caption = "Customer details"
    Me.txtName.Value = vbNullString
End Sub

The Me keyword refers to the current form. Make sure the control names in code match the controls’ (Name) properties. A missing or misspelled control name causes a compile error. Initialize is intended for setup when the form is loaded; it does not necessarily run again every time you hide and then show the same still-loaded form. Use the UserForm_Activate event for work that should happen when the form becomes active, bearing in mind that activation can occur more than once.

To pass a value into a form, set a public property or control value before showing it, or write a public setup procedure in the form’s code module. For example, a macro can assign a worksheet value to a text box before calling Show. Keep data-handling logic in named procedures where practical rather than putting a long sequence of worksheet operations into a button-click event. This makes it easier to review what happens when a user submits the form.

Hide, Unload, and Close a Form

Closing a form and hiding it are different. Hide removes the form from view but keeps the loaded form and its control values available, so code can show it again without rebuilding its state. Unload removes the form from memory and resets its controls when it is loaded again. Choose based on whether you need to preserve the user’s entries.

For example, a Cancel button can hide a form if another procedure will decide what to do next, or unload it if the form should be discarded. A submit button might read the inputs, write validated values to the intended cells, and then unload the form. If you rely on the form’s title-bar close button, be aware that it closes the form rather than automatically saving its contents to a worksheet. Add explicit Save, Submit, or Cancel actions when the distinction matters to users.

After a modal call such as frm.Show vbModal, the calling procedure pauses until the form is hidden or unloaded. Once it returns, code after Show can run—for example, to check a result or release the form. A modeless call returns immediately, while the form remains on screen. That difference affects where you put validation and cleanup code.

Common Problems and Fixes

“Variable not defined” or “User-defined type not defined”: Check that the form exists in the active VBA project and that its (Name) property matches the code. A visible caption is not necessarily its code name.
The macro does not appear in Alt+F8: Confirm that the procedure is Public, is in a standard module, and has no required arguments. A procedure in a worksheet or UserForm module, or one declared Private, may not be listed as a normal runnable macro.
The button does nothing: Check that the button is assigned to the intended macro, macros are enabled for this trusted workbook, and the file is opened in desktop Excel rather than Excel for the web.
A control reference fails: Verify the control’s (Name) property in the Properties window. For example, code referring to txtName will not find a text box named TextBox1.
The form opens but the workbook remains blocked: A modal form is doing that by design. Close or hide it, or use vbModeless if the user must work in the sheet at the same time.

For compile errors, use Debug > Compile VBAProject in the Visual Basic Editor to identify issues before running the form. Test with a copy of the workbook, especially if the form writes to cells, clears ranges, or changes workbook settings. VBA macros cannot be undone with Ctrl+Z, so save a separate copy before running or modifying a macro that changes data.

Excel for Mac and Other Excel Versions

The basic VBA pattern, UserFormName.Show, is the same in desktop Excel for Windows and Mac. On Windows, Alt+F11 opens the Visual Basic Editor; on many Mac keyboards, Option+F11 opens it, and some keyboards require Fn as well. Mac menu and keyboard behavior can vary by keyboard settings and Excel release. Excel for Mac supports VBA, but particular controls and platform-specific features may not be available or may behave differently. In particular, do not treat worksheet ActiveX controls as a cross-platform solution.

Excel for the web can open and edit many workbooks, but it cannot execute VBA macros or display a VBA UserForm. A user who needs to launch the form must open the workbook in desktop Excel with macros permitted by the organization’s security settings. Macro security is intentional: only enable macros in files you trust, because VBA can make changes to the workbook and interact with other files or applications.

Frequently Asked Questions

What is the command to open a VBA UserForm?
Use UserFormName.Show, replacing the name with the form’s (Name) property. For example, frmCustomer.Show opens a form named frmCustomer.

How do I open a UserForm automatically when a workbook opens?
Put a call to the form in the Workbook_Open event in the ThisWorkbook code module. For example, Private Sub Workbook_Open() can call frmCustomer.Show. Save the file as .xlsm, and remember that the event only runs when macros are enabled and the workbook is opened in desktop Excel. Consider whether users should be able to dismiss or bypass the form.

Why does my UserForm open behind Excel or block the worksheet?
A modal form blocks interaction with the workbook until it is closed or hidden. Use Show vbModeless when users need to work in the sheet while the form stays visible. Window stacking can also depend on other open windows and the platform.

How can I reopen a form without clearing its entries?
Use Hide rather than Unload when you want the loaded form and its control values to remain available. Showing that same instance again can preserve its values. Unloading it discards the loaded instance; a newly loaded form is initialized again.

Can I open a VBA UserForm in Excel for the web?
No. Excel for the web does not run VBA macros or show VBA UserForms. Open the workbook in desktop Excel for Windows or Mac to use the form, subject to the workbook’s macro settings and the form’s platform compatibility.

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.