Excel VBA: How to Open a UserForm

In Excel VBA, open a UserForm by calling its Show method from a macro or another procedure. Use a modal form to require users to finish or close it before returning to the worksheet, or a modeless form when they need to keep working in Excel while it remains open.

Sub OpenEntryForm()
    Dim frm As UserForm1

    ' Create and show a separate instance of the form.
    Set frm = New UserForm1
    frm.Show vbModal
End Sub

Replace UserForm1 with the form’s actual name. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

What the Show method does

A UserForm is a custom dialog or panel built in the Visual Basic for Applications (VBA) editor. It can contain controls such as text boxes, labels, buttons, check boxes, and lists. The form itself does not appear just because it exists in the workbook; VBA must show it. The Show method makes the form visible and starts its interaction with the user.

In Excel VBA, a form can be referenced by its name, such as UserForm1. Calling its Show method displays it. The method accepts an optional argument that sets its display mode: vbModal or vbModeless. If you omit the argument, the form is modal by default. A form’s name is not necessarily the text displayed in its title bar: the (Name) property identifies the form in code, while the Caption property controls its visible title.

The sample above explicitly creates a new instance with New and stores it in a variable. A shorter pattern uses the form’s default instance, which VBA makes available using the form’s name. Default instances are convenient for small forms, while explicit instances are useful when you want to manage a particular form object or create more than one instance.

Create and name the UserForm

In desktop Excel for Microsoft 365 on Windows, open the workbook in Excel, choose Developer > Visual Basic, then use Insert > UserForm in the Visual Basic Editor. If the Developer tab is not visible, enable it in Excel’s ribbon settings. The editor adds a blank form to the workbook’s VBA project; the Toolbox and Properties window let you add controls and set their properties.

Select the new form and find its (Name) property in the Properties window.
Give it a descriptive code name, such as frmOrderEntry. Use that exact name in VBA.
Set the Caption property to the title users should see, such as Enter an order.
Add the required controls, and give important controls clear names, for example txtCustomer or cmdSubmit.

Changing a form’s caption does not change the name VBA uses to open it. If code refers to UserForm1 after you changed the form’s (Name) to frmOrderEntry, VBA will not find an object with the old name. Update the code to use the new code name.

Open it from a macro or worksheet button

A common arrangement is to put the opening procedure in a standard module. In the Visual Basic Editor, choose Insert > Module, then add a procedure like the example above. After changing the form name if needed, run the macro from Excel’s Developer > Macros dialog, or call it from another VBA procedure. The Show line is what displays the form; merely declaring or creating a form object does not make it visible.

To let a user open the form from a worksheet, connect the macro to a button or shape:

Insert a Form Control button from Developer > Insert, then assign the opening macro when prompted.
Alternatively, right-click a worksheet shape and choose Assign Macro, then select the macro that opens the form.

When the user clicks the control, Excel runs the assigned procedure. This is often easier to use than asking someone to open the Macro dialog. A worksheet button does not contain the form itself; it simply runs the VBA procedure that displays it.

You can also open a form in response to another VBA event, such as a workbook event or a button click on a different UserForm. The same basic rule applies: the event procedure must call the form’s Show method. Avoid putting an opening call in an event that fires repeatedly, such as a worksheet-change event without conditions, or users may see the form more often than intended.

Choose modal or modeless behavior

A modal form is the usual choice for a short data-entry task, confirmation, or required choice. While it is open, the user must dismiss it before interacting with the workbook window behind it. In a procedure that shows a modal form, execution pauses at the Show call until the form is hidden or unloaded. The first code sample uses vbModal explicitly, although leaving off the argument produces the same default behavior.

A modeless form stays open while the user can select cells, switch worksheets, or use other parts of Excel. In this mode, VBA continues past the Show call immediately, so code after that line should not assume the user has already filled in the form. For a modeless form, keep a reference to the form in a module-level variable so the code can manage the instance after the opening procedure ends:

Private mEntryForm As UserForm1

Public Sub OpenEntryFormModeless()
    ' Retain a reference while the modeless form is open.
    Set mEntryForm = New UserForm1
    mEntryForm.Show vbModeless
End Sub

A modeless form can be useful as a small tool panel, but it needs more deliberate state management. For example, consider what should happen if the user clicks the opening button twice, changes the active sheet, or edits data while the form is still open. If those situations are not needed, a modal form is usually simpler.

Initialize controls when the form opens

Use the UserForm’s Initialize event to set initial values when a new instance is created. This event runs before the form is displayed for the first time. For example, if the form contains a text box named txtDepartment, place this event procedure in that form’s code module:

Private Sub UserForm_Initialize()
    Dim defaultDepartment As String

    ' Set a starting value for the named text box.
    defaultDepartment = "Sales"
    Me.txtDepartment.Value = defaultDepartment
End Sub

The control name in code must match the control’s (Name) property. The Me keyword refers to the current form instance, which is helpful when you create forms explicitly. Initialization is a good place for fixed defaults or for loading a short list. If the form needs data from the selected row, it is often clearer to pass or assign that data before calling Show, rather than relying on whichever cell happens to be active later.

Do not confuse initializing a form with showing it. An instance may be created and prepared without being visible; the Show method makes it appear. Also, if you hide an existing instance and later show that same instance again, its controls may retain their previous values. Decide whether that is desirable, and reset the controls when reopening if each display should start fresh.

Hide the form or unload it

Hiding a form makes it invisible but keeps the instance and its control values available. This is useful when a procedure needs to read values after the user makes a choice, or when you intend to show the same form again without rebuilding it. In the form’s own event code, Me.Hide hides the current instance. A typical pattern is for a button to validate the entries, store or use the values, and then hide the form.

Unloading a form removes that instance from memory and releases its controls. The user can close a form with its close button, or VBA can unload it using the Unload statement. If you need values after the form is dismissed, capture them before unloading; once the instance is gone, its control values are no longer available through that instance. For an explicit instance, clear or replace the variable reference as appropriate when your code is finished with it.

Use Hide when the form is temporarily out of view and its contents should be preserved. Use Unload when the interaction is complete and the form should be discarded. This distinction is a common source of unexpected behavior: reopening a hidden form can reveal old entries, while unloading and creating a new instance generally starts with freshly initialized controls.

Common problems and practical fixes

“Variable not defined” or “Method or data member not found”: Check the form’s (Name) property and spelling. The caption shown to users is not the code name.
The macro does not appear in the Macros dialog: Put a public, parameter-free opening procedure in a standard module. A procedure in a form’s code module or one declared Private is not generally available for direct assignment like a normal public macro.
The form opens more than once: Check whether the opening procedure runs repeatedly or whether each call creates a new instance. Decide whether to reuse one instance or prevent a second instance from being created.
The form appears behind Excel or seems to block the workbook: Confirm whether it is modal or modeless. A modal form intentionally prevents interaction with the workbook until it is dismissed.
Changes to controls appear to vanish: Check whether code unloads the form before reading its values, or creates a fresh instance each time. Store needed values before unloading.
The macro is blocked: Confirm that the workbook is macro-enabled and that its VBA project is trusted. Do not enable macros in files from unknown or untrusted sources just to display a form.

When debugging, put the cursor inside the opening procedure and use the Visual Basic Editor’s Run command, or step through the procedure with its debugging controls. If the code stops on the Show line, inspect the highlighted form name and confirm the form exists in the workbook’s VBA project. A compile error elsewhere in the project can also prevent a macro from running, even if the form code itself looks correct.

Workbook format, macros, and platform limits

Save a workbook containing VBA in a macro-enabled format such as .xlsm or in an Excel Macro-Enabled Template format when that is appropriate. Saving as .xlsx does not preserve VBA macros; Excel warns that macro content will be removed if you continue in that format. A form that relies on VBA cannot run in Excel for the web, which does not execute VBA macros. Users need a desktop version of Excel with macros enabled to use it.

Excel for Mac includes VBA and supports UserForms, but operating-system-specific code and some controls or integrations designed for Windows may not behave the same way. Test the form on the platforms where the workbook will be used, especially if it relies on Windows APIs, external components, or controls beyond basic text boxes, labels, and buttons. In Excel for Windows, Developer > Visual Basic opens the editor and Alt+F11 is a common shortcut. On Mac, use the Developer tab’s Visual Basic command; keyboard shortcuts can vary with the keyboard and function-key settings. Excel for the web has no VBA editor or VBA UserForm support.

For basic cross-platform data entry, keep the form’s controls and code straightforward, avoid Windows-only dependencies, and test a copy of the workbook on each target desktop platform. A UserForm is a VBA interface inside the workbook, not a browser-based dialog and not a replacement for a web form.

Frequently Asked Questions

What is the VBA command to open a UserForm?
Call the form’s Show method from a VBA procedure. The form name in that call must match its (Name) property in the Visual Basic Editor.

How do I open a UserForm when a worksheet button is clicked?
Assign a public opening macro in a standard module to a Form Control button or worksheet shape. Clicking that control runs the macro, which displays the form.

How do I open a UserForm without blocking the worksheet?
Show it with the vbModeless argument. The user can then work in Excel while the form remains open; make sure the code manages the form’s lifetime and any changes made while it is displayed.

Why does my UserForm not open?
Check the form’s code name, confirm that the opening macro is in the workbook’s VBA project, and verify that macros are enabled for a trusted file. Also check for compile errors and confirm you are using desktop Excel rather than Excel for the web.

Can Excel for the web open a VBA UserForm?
No. Excel for the web does not run VBA macros or display VBA UserForms. Open the macro-enabled workbook in desktop Excel to use the form.

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.