Excel UserForm: How to Show It with VBA

VBA & macros · Updated November 18, 2026

To show an Excel UserForm, create the form in the Visual Basic Editor, then run a VBA procedure that calls its Show method—for example, UserForm1.Show. In Excel for Microsoft 365 on Windows, open the editor with Alt+F11, add a UserForm, and run a macro that displays it; Excel for the web cannot run VBA UserForms.

What an Excel UserForm is

A UserForm is a custom dialog window built with VBA. It can collect information, display choices, or guide someone through a task without asking them to work directly in a worksheet. A form might contain text boxes for a customer’s name and email address, a list box for a product category, and buttons to submit or cancel.

Showing the form is only one part of the process. The form must exist in the workbook’s VBA project, and some action must call its Show method. That action can be a macro the user runs from Excel, a worksheet button assigned to a macro, or another VBA event. Creating a UserForm does not automatically make it appear when the workbook opens.

UserForms are useful when a worksheet interface would be confusing or when you want to validate entries before putting them into cells. They do require macros, however, so they are not a way to add an interactive form to a workbook used in Excel for the web.

Create a UserForm in the Visual Basic Editor

In desktop Excel for Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. You can also select Developer > Visual Basic. If the Developer tab is missing, enable it through File > Options > Customize Ribbon, then select the Developer check box.

  1. In the Visual Basic Editor, select the workbook project in the Project Explorer.
  2. Choose Insert > UserForm. Excel adds a new form, commonly named UserForm1.
  3. If the Toolbox is not visible, choose View > Toolbox. Drag controls such as a Label, TextBox, ComboBox, or CommandButton onto the form.
  4. Open the Properties window, usually with F4, to change settings such as the form’s (Name) and Caption.
  5. Double-click a control to create or edit its event procedure, such as what happens when a button is clicked.

The (Name) property is the identifier VBA uses in code; Caption is the text people see in the window’s title bar. For example, a form named frmCustomer can have the caption Customer details. Naming forms and controls descriptively makes code easier to understand than leaving every object with a default name.

Show the form with a macro

To make a form appear on demand, add a standard module in the Visual Basic Editor with Insert > Module. Add a procedure that creates and displays the form. The sample below assumes the form’s (Name) is still UserForm1:

Public Sub ShowEntryForm()
    Dim frm As UserForm1

    ' Create an instance of the form and display it.
    Set frm = New UserForm1
    frm.Show vbModal
End Sub

Run the procedure from Excel by selecting Developer > Macros, choosing ShowEntryForm, and selecting Run. You can also place the cursor inside the procedure in the Visual Basic Editor and press F5. If you renamed the form, replace UserForm1 in the code with its actual (Name) property.

The example uses a new instance of the form and displays it modally. You can also use the form’s default instance with UserForm1.Show; if no argument is supplied, the form is modal by default. Explicitly writing vbModal makes the intended behavior clear to readers of the code.

Choose modal or modeless behavior

A modal form stays in front and prevents the user from interacting with the rest of Excel until it is hidden or unloaded. This is usually appropriate for a short data-entry dialog: the person completes the form, submits or cancels, and then returns to the worksheet. Modal behavior also reduces the chance that the person will change the wrong cell while a form is waiting for input.

A modeless form lets the user keep working in Excel while the form remains open. To show the default instance modelessly, call UserForm1.Show vbModeless. This can be useful for a small tool that people refer to while selecting worksheet cells, but it calls for more care: the active cell can change while the form is open, and the form may need to stay available as the user navigates.

Choose the behavior based on the task, not just on appearance. A required prompt that must be answered before continuing is generally modal. A palette or helper window used alongside worksheet work may be modeless. In either case, provide a clear way to finish or dismiss the form. A button procedure can use Unload Me to close and remove the current form, or Me.Hide to hide it while keeping its current values in memory. Hiding and unloading are not interchangeable: an unloaded form is no longer available as that loaded instance.

Make the form easy to launch

Running a macro from the Macros dialog is convenient during development, but workbook users may prefer a visible button. On a worksheet, select Developer > Insert > Button under Form Controls, draw the button, and choose the display macro when Excel asks which macro to assign. Change the button label to something direct, such as Open entry form. Clicking it then runs the macro and shows the UserForm.

A Form Control button is often simpler than putting a button inside the form or using an ActiveX control on the worksheet. The worksheet button starts the macro; controls inside the UserForm handle the choices and actions within the dialog. In the form’s code, a CommandButton’s Click event can validate entries, transfer values to a worksheet, or close the window. The form does not write data to cells merely because it has been shown: that behavior has to be programmed.

You can also call the display procedure from another macro or from a suitable workbook or worksheet event. Automatically displaying a form when a workbook opens can surprise users and may be blocked if macros are disabled. If you choose that design, explain what the form does and make sure the workbook remains usable if the user closes it.

Common problems when a UserForm will not show

If running the macro produces no visible form or an error, check the setup in a deliberate order. Many problems come from a mismatch between the form’s name and the code, or from running the wrong procedure.

  • Check the form name. In the Properties window, confirm the form’s (Name) and compare it with the identifier used before .Show. The caption displayed at the top is not necessarily the VBA name.
  • Check where the macro is stored. A launch macro should normally be in a standard module, and the procedure should be a public, parameter-free Sub if it is to appear in the Macros dialog or be assigned to a worksheet button.
  • Check macro security and file type. A workbook saved as .xlsx cannot retain VBA code. Save a macro-enabled workbook as .xlsm, then allow macros only when the file and its source are trusted and your organization’s security rules permit it.
  • Look for a hidden or off-screen window. A modeless form may be behind another window or appear on a different monitor after a display setup changes. Bring Excel and the form forward, and test a modal display to distinguish a visibility issue from a macro that did not run.
  • Check for a code error. In the Visual Basic Editor, use Debug > Compile VBAProject if available and inspect highlighted code or the error message. Confirm that controls referenced in event code still have the names used by that code.
  • Check the Excel environment. A workbook opened in Excel for the web will not run its VBA UserForm. Open it in desktop Excel instead. Also check whether the workbook opened in Protected View or whether organizational policy prevents macros from running.

If a form appears and immediately disappears, inspect the macro that calls it and any events that run during initialization. A procedure may be hiding or unloading the form, or an error in an event may interrupt setup. Use the Visual Basic Editor’s debugging tools to locate the line that fails rather than repeatedly changing unrelated display settings.

Save, share, and use UserForms safely

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if it contains VBA you need to keep. A standard .xlsx file does not store VBA macros. Before running unfamiliar macros, consider what code they contain and where the file came from; a macro can make changes beyond simply displaying a form. Do not enable macros solely because a workbook asks you to.

Before running a macro that changes worksheet data, save a separate copy of the workbook. VBA actions generally cannot be reversed with Excel’s Undo command (Ctrl+Z on Windows), so a backup is a practical safeguard while developing and testing. Test the form with representative entries, including blank or invalid input, and verify where submitted values go.

Excel for the web does not execute VBA, so a workbook opened there cannot show its VBA UserForm. Excel for Mac has desktop VBA support, but the VBA editor, keyboard shortcuts, and some control behavior can differ from Windows. In particular, do not assume that every Windows-specific ActiveX control or API will work on a Mac. If a workbook must work on both platforms, keep the design to supported controls and test it in each desktop environment. A Mac keyboard may require the function key or a different shortcut setting to use function-key combinations; the Visual Basic Editor can also be opened through Excel’s menus.

When to use a different approach

A UserForm is a good fit when a workbook needs a custom VBA-powered dialog and users run it in desktop Excel with macros enabled. It is less suitable when recipients use Excel for the web, cannot run macros, or need a form that works across many devices without desktop-specific code.

For simple tabular entry, a clearly designed worksheet with data validation may be easier to share and maintain. Excel’s built-in data validation can restrict entries or offer a drop-down list without a custom VBA window. If a workflow must collect responses outside the workbook, a web-based form or an organization-approved data collection tool may be a better fit. These alternatives have different capabilities; they do not turn a VBA UserForm into a web form.

A common misconception is that the UserForm designer itself creates a complete data-entry system. It creates the interface, but you still need VBA to check the input, decide what happens on Submit or Cancel, and place results in the intended location. Keeping those responsibilities explicit makes the workbook easier to troubleshoot and safer to use.

Frequently Asked Questions

How do I show a UserForm in Excel?
In desktop Excel, create the form in the Visual Basic Editor, then run a macro containing UserForm1.Show or call the form’s Show method using its actual VBA name. You can run the macro from Developer > Macros or assign it to a worksheet button.

Why is my UserForm not listed in the Macros dialog?
The Macros dialog lists runnable public procedures, not UserForms themselves. Put a public, parameter-free Sub in a standard module and have that procedure call the form’s Show method.

How do I show a UserForm automatically when a workbook opens?
You can call a display macro from the workbook’s Workbook_Open event in the ThisWorkbook module. Users must open the file in desktop Excel and permit its macros to run, so consider whether automatic display is necessary and provide a clear way to close the form.

Can I show an Excel UserForm without VBA?
No. A VBA UserForm is part of Excel’s VBA feature set and requires VBA to display and operate it. Worksheet data validation or an external form tool may suit a task that must work without macros.

Will a UserForm work in Excel for the web?
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.

What is the difference between Hide and Unload?
Me.Hide makes the form invisible while keeping the loaded form and its values available. Unload Me closes and removes that loaded instance; showing it again creates or loads a form anew, so its controls may return to their initial values.

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.