VBA Show UserForm: Open a Form in Excel

VBA & macros · Updated November 18, 2026

To show an Excel VBA UserForm, call its Show method, usually from a standard module: UserForm1.Show. In Excel for Microsoft 365 on Windows, that opens the form modally by default, so the user must close or hide it before working in the workbook again.

The basic Show command

UserForm1 is an example name, not a special VBA keyword. Replace it with the form’s actual (Name) property from the Properties window in the Visual Basic Editor (VBE). The form’s Caption property controls the text shown in its title bar; changing the caption does not change the name used in code.

For a straightforward launch, put a procedure in a standard code module and call the form’s Show method:

Option Explicit

Public Sub ShowEntryForm()
    ' Display the form and wait for the user to close or hide it.
    UserForm1.Show vbModal
End Sub

To add this procedure, press Alt+F11 in Excel for Windows to open the VBE, choose Insert > Module, and paste the code into the module. If the form is named something else, substitute that name. You can run ShowEntryForm from the VBE or assign it to a workbook button.

Create a UserForm in Excel

A UserForm is a custom dialog window made with VBA controls such as text boxes, labels, list boxes, and command buttons. It is useful when a workbook needs a guided way to collect or display information instead of asking people to edit cells directly. UserForms are available in desktop Excel, but Excel for the web cannot create or run VBA macros.

  1. Open the VBE with Alt+F11. If the Developer tab is missing, enable it through File > Options > Customize Ribbon and select Developer.
  2. In the VBE, choose Insert > UserForm. The form designer opens, along with a toolbox for adding controls.
  3. Select the form and press F4 to view its Properties window. Set its (Name) to a clear identifier, such as frmEntry, and set Caption to text people will understand, such as “Enter a record.”
  4. Add controls from the toolbox and set their names and captions. For example, a text box named txtCustomer is easier to recognize in code than the default name TextBox1.
  5. Use Insert > Module to add the launch procedure, then change the example form name in the procedure to match the form’s (Name) property.

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains VBA. A regular .xlsx workbook cannot store VBA project code; saving a macro-enabled file in the wrong format can remove the project.

Modal versus modeless forms

The Show method accepts a mode. vbModal makes the form modal: while it is open, the user cannot select cells or interact with the workbook window behind it. This is generally appropriate for a required choice, a short data-entry dialog, or a warning that must be acknowledged. It is also the default, so UserForm1.Show and UserForm1.Show vbModal have the same basic behavior.

A modeless form stays open while the user can also work in Excel. Use this when a small tool needs to remain visible while someone selects cells or compares values. The call is:

Option Explicit

Public Sub ShowToolsForm()
    ' Leave Excel available while the form remains open.
    UserForm1.Show vbModeless
End Sub

Modeless forms need more careful design. The user can change the workbook while the form is open, so code should not assume the active cell or selection remains unchanged. A modeless form also does not pause the launching procedure in the way a modal form does. Avoid opening repeated copies every time a launch button is clicked; a single maintained form instance is usually easier to manage.

Launch the form from a worksheet button

A button makes a form discoverable to people who do not use the VBE. First create a public launch macro in a standard module, such as ShowEntryForm above. Then, in Excel for Windows, select Developer > Insert > Button (Form Control), draw the button on the worksheet, and choose the launch macro in the Assign Macro dialog. Edit the button text to describe the action, for example, “Add record.”

You can also assign a macro to a shape: insert or select a shape, right-click it, choose Assign Macro, and select the procedure. A Form Control button or shape is often a simple choice for a worksheet interface. ActiveX controls are a separate option with additional design and event-code details; they are not necessary just to run a form. If you use the Mac version of Excel, the VBE and macro features are available in desktop Excel, but menu layout and keyboard shortcuts can differ. Excel for the web does not run these VBA controls or macros.

What happens when a form opens and closes

Showing a form involves more than drawing its window. When a form is first loaded, its Initialize event runs before it appears. That event is a suitable place to set an initial caption, populate a list, or prepare controls. For example, in the form’s code module:

Option Explicit

Private Sub UserForm_Initialize()
    Dim formTitle As String

    ' Set a readable title before the form is displayed.
    formTitle = "Enter a record"
    Me.Caption = formTitle
End Sub

Me refers to the form whose code is running. The Initialize event is not the same as the Activate event: Activate can run again when a loaded form becomes active, so it is not always the right place for work that should happen only once per load.

Closing and hiding also differ. Me.Hide removes the form from view but keeps that instance loaded, including its current control values. Showing the same instance again can therefore preserve those values unless your code resets them. Unload Me unloads the form; when it is loaded again, Initialize runs again and controls return to their initial state. The title-bar close button normally closes the form by unloading it.

Choose deliberately. A Cancel button that should discard the current entry can unload the form, while a temporary hide-and-return workflow may call for Hide. If a form is hidden and then shown again, check whether retained values are desirable; otherwise, clear or reset the relevant controls before displaying it.

Passing information to a form

For a small workbook, the form can read or write worksheet cells directly in its button-event procedures. For a more reusable design, keep the form focused on collecting input and let a standard module or a separate procedure handle saving that input. This makes it easier to validate data before changing the sheet and reduces the form’s dependence on whichever worksheet happens to be active.

Do not rely on ActiveCell as a permanent destination if users can move around the workbook, especially when the form is modeless. Store the intended row or worksheet explicitly when the form opens, or pass the relevant information through a procedure or a property. For example, a form used to edit a selected record should retain that record’s identifier rather than infer it from the current selection later.

When you need multiple independent copies of a form, create explicit instances rather than relying on the form’s default instance. That approach requires managing object references and deciding when each instance is unloaded. For a typical one-form data-entry workflow, the default instance syntax such as UserForm1.Show is simpler and sufficient.

Common problems and fixes

  • “Object required” or a form does not open: Check that the code uses the form’s (Name) property, not its Caption, and that the spelling is exact. Also confirm the form exists in the current VBA project.
  • The macro is missing from Assign Macro: The launch procedure should generally be a Public Sub in a standard module, with no arguments. A procedure placed in a worksheet, ThisWorkbook, or UserForm code module may not appear as an assignable macro in the same way.
  • The form appears to freeze Excel: It may be modal, which blocks interaction with the workbook by design. If users must work in the sheet while the form remains open, use vbModeless and make the form’s behavior safe for changing selections.
  • Changes disappear when the form is shown again: The form may have been unloaded and recreated. Use Hide if you need to retain its current values, or deliberately save and restore values when reopening it.
  • Nothing runs after opening the workbook: Check whether the file is saved as .xlsm and whether Excel has blocked macros. Do not enable all macros globally just to make one workbook work; follow your organization’s policy and only use files you trust.

When a problem is unclear, use the VBE’s Debug > Compile VBAProject command to identify compile errors, then run the launch procedure from the VBE so an error message can point to the failing line. A compile check can catch issues such as misspelled procedure names before the form is used.

Save safely and understand platform limits

Before running a macro that changes workbook data, save a separate copy of the workbook. VBA actions cannot be undone with Ctrl+Z; Excel’s normal undo history does not reverse most macro changes. Test data-entry and save routines on sample records first, particularly if they overwrite cells, delete rows, or update several sheets.

On Windows, open the VBE with Alt+F11. On many Mac keyboards, the shortcut is Option+F11, sometimes with the Fn key depending on keyboard settings; the Developer tab or Excel’s Tools menu can also provide access to the Visual Basic Editor. The desktop Mac version supports VBA, but differences in controls, file paths, and operating-system features can affect code portability. Excel for the web does not run UserForms or VBA, so a workbook that depends on them needs desktop Excel for that workflow.

When to use a UserForm instead of another tool

A UserForm is a good fit when a workbook needs a custom VBA-based dialog, local input validation, or controls arranged for a particular task. It is not automatically the best choice for every data-entry need. A worksheet table with data validation may be easier to maintain when users can enter records directly, and an Office Script or a web-based form may be more appropriate when browser use or cross-platform access is essential. Those alternatives are not drop-in replacements: they have different capabilities and do not display an Excel VBA UserForm.

Think about who will use the workbook, which Excel platforms they have, and whether macros are permitted before building a form-dependent process. If recipients use Excel for the web or have macros disabled by policy, a UserForm-based interface will not be available to them. A simple worksheet layout can provide a useful fallback for entering or reviewing data.

Frequently Asked Questions

How do I show a UserForm in Excel VBA?
Call the form’s Show method, for example UserForm1.Show, from a macro or other VBA procedure. Replace UserForm1 with the form’s (Name) property.

What is the VBA code to show a UserForm modelessly?
Use UserForm1.Show vbModeless. The form remains open while the user can interact with Excel. Use vbModal or the default Show behavior when the workbook should remain blocked until the form is closed or hidden.

Where should I put the code that opens a UserForm?
For a macro assigned to a worksheet button, put a public, argument-free launch procedure in a standard module. The form’s own code module is where you put event procedures such as Initialize or a command button’s Click event.

Why does my UserForm not show?
Verify the form’s name, make sure the launch macro is in the correct workbook project, and check for compile errors. Also confirm macros are allowed for that trusted workbook and that you are using desktop Excel rather than Excel for the web.

Does hiding a UserForm reset its fields?
No. Hide keeps the form loaded, so control values usually remain. Unload removes the instance; loading it again initializes a new form state. Clear fields explicitly if you want a hidden form to reopen blank.

Related

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.