VBA UserForm: How to Show It in Excel

VBA & macros · Updated November 18, 2026

To show a VBA UserForm in Excel, call its name followed by .Show, such as UserForm1.Show. Add vbModeless when users should be able to work in the worksheet while the form remains open.

Create a UserForm in the VBA Editor

A UserForm is a custom window built in the Visual Basic for Applications (VBA) editor. It can gather input, display information, or provide controls for a workbook task. Before you can show one, the workbook needs a form component: open the editor with Developer > Visual Basic, then choose Insert > UserForm in the editor. If the Developer tab is not visible in Excel for Windows, enable it through File > Options > Customize Ribbon.

The editor creates a form, usually named UserForm1, and opens its design surface. Use the Toolbox to add controls such as text boxes, labels, and command buttons. You can change the form’s name and caption in the Properties window. The name is the identifier VBA uses in code; the caption is the text displayed in the form’s title bar. If you rename the form to frmCustomer, use that name in the Show statement instead of UserForm1.

To create a simple launcher, insert a standard module using Insert > Module in the VBA editor and put a public macro in it. This example creates a separate form instance and displays it modally:

Public Sub ShowCustomerForm()
    Dim customerForm As UserForm1

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

Replace UserForm1 with the actual form name. A new instance is useful when you want each opening to start with a fresh form rather than reusing the form’s default instance.

What the Show Method Does

The Show method loads and displays a UserForm. For a quick macro, the default instance can be shown directly:

Public Sub OpenEntryForm()
    ' Display the default instance of the form.
    UserForm1.Show
End Sub

With no argument, Show uses modal behavior. That means the form must be dismissed or hidden before the user can interact with the Excel application outside it. A modal form is often suitable for a short data-entry task where the user must make a choice before continuing.

The statement does not place the form on a worksheet or create a worksheet control. It opens a separate window over Excel. The form’s position, size, controls, and behavior come from its design and code. A form can also be displayed from another form, a worksheet button’s assigned macro, or other VBA procedures, provided the calling code can access it.

Choose Modal or Modeless Behavior

Use vbModal when the form should temporarily block interaction with the rest of Excel. This is the default, so UserForm1.Show and UserForm1.Show vbModal have the same modal effect. The macro that called a modal form waits at the Show statement until the form is hidden or unloaded.

Use vbModeless when users need to select cells, edit a worksheet, or use other Excel windows without closing the form. For example:

Public Sub OpenFloatingForm()
    ' Keep the form open while allowing worksheet interaction.
    UserForm1.Show vbModeless
End Sub

A modeless form is useful as a floating helper or search panel, but it needs thoughtful design. Users can change the workbook while the form is open, so code should not assume that the active sheet, selected cell, or current workbook remains unchanged. Refer to a specific workbook and worksheet where possible instead of relying on whichever sheet happens to be active.

Modeless forms also need a clear way to close or hide them. Add a command button and put an event procedure in the form’s code module. For a button named cmdClose, the event can be:

Private Sub cmdClose_Click()
    ' Close this form and release its resources.
    Unload Me
End Sub

The word Me refers to the current form instance. The control’s name in the event procedure must match its actual name in the Properties window.

Open a Form from a Worksheet Button

A launcher button lets users open a form without visiting the VBA editor. On Windows, choose Developer > Insert > Button in the Form Controls section, draw the button on the worksheet, and assign a public macro such as OpenEntryForm. The button runs that macro when clicked. You can change its displayed label by right-clicking it and editing the text.

Form Controls are different from ActiveX controls. For a simple button that runs a macro, a Form Control is usually the more straightforward choice. An ActiveX command button instead uses an event procedure such as CommandButton1_Click on the worksheet module, and it has design-mode and platform considerations. On Excel for Mac, do not assume Windows ActiveX controls will work; ActiveX is not supported in Excel for Mac. A macro assigned to a Form Control is a more portable approach, though users still need a desktop Excel version that can run VBA.

Another option is to add the macro to the Quick Access Toolbar or run it from the Macros dialog. A worksheet button is often easier for people who use the workbook regularly, while a toolbar command avoids adding an object to the sheet.

Initialize Controls and Handle Form Data

VBA provides form events that let you prepare controls or respond to actions. The UserForm_Initialize event runs when the form is initialized, before it is shown. For example, if the form contains a text box named txtName, you can set its starting value like this:

Private Sub UserForm_Initialize()
    Dim startingText As String

    ' Start with an empty name field.
    startingText = vbNullString
    Me.txtName.Value = startingText
End Sub

Use this event for initial values or for loading a list of options. If the form is hidden and shown again without being unloaded, it may retain its existing control values; initialization is not a general-purpose reset that runs every time a visible form is reopened. If the form must clear or refresh its fields each time, write a separate reset procedure and call it at the appropriate point.

For a form with an OK button, its click event can validate a field, copy the value to a worksheet, and then close the form. Keep the destination explicit. For example, code that writes to ThisWorkbook.Worksheets("Data") targets the sheet named Data in the workbook containing the VBA project. This is safer than relying on ActiveSheet, which can change while a modeless form is open. Add checks for missing sheets or invalid input when the workbook will be used in different contexts.

Hide, Unload, and Reopen a Form

Hide makes a form invisible but keeps its instance and control values in memory. This is useful when a user may return to the form and you want to preserve their entries. Unload removes the form instance and releases its resources; opening it again creates or loads a form that may need to be initialized again. These actions are not interchangeable.

For example, a Cancel button can either discard the current form instance with Unload Me or hide it temporarily with Me.Hide. Choose based on what Cancel should mean in your workflow. If the form is hidden and the user later reopens the same instance, clear or reload values deliberately rather than assuming the controls reset themselves.

With a separately created form instance, keep in mind that code needs a reference to that instance to work with it later. For a basic one-form launcher, using the default instance is simpler. For more advanced workflows that open multiple forms or retain modeless instances, manage references and closing behavior carefully so the form does not disappear unexpectedly or remain open after the task is complete.

Common Problems and How to Fix Them

  • “Variable not defined” or “Sub or Function not defined”: Check that the form’s name in code matches its (Name) property, and that the launcher macro is in a standard module when you intend to assign it to a worksheet button.
  • The macro does not appear in the button’s Assign Macro list: Make the launcher a public procedure with no arguments in a standard module. A private procedure, a procedure requiring parameters, or an event procedure is not a suitable direct assignment.
  • The form appears, then disappears or behaves unexpectedly: Check whether the code uses a separate instance or the default instance, and whether it unloads or hides the form. For a modeless form, plan how its instance will remain available and how users will close it.
  • The form will not open when the workbook is shared: Confirm that macros are enabled under the user’s security policy and that the file is saved in a macro-enabled format. Do not ask users to enable macros in files they do not trust.
  • The code refers to a missing control: Confirm the control’s (Name) property, not just its visible caption. A button caption can say “Close” while its actual code name is still CommandButton1.

VBA is not supported in Excel for the web, so a workbook’s macros and UserForms cannot run there. The file must be opened in a desktop Excel application. Also check that the workbook contains the form and code, rather than just a worksheet button that points to a macro absent from the file.

Save and Run the Macro Safely

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) or another format that preserves VBA, such as an Excel Binary Workbook (.xlsb). Saving it as a standard .xlsx file does not preserve VBA code. When a workbook is opened, Excel may block macros depending on the file’s origin and security settings; follow your organization’s policy and use macros only from trusted sources.

Before running a macro that changes workbook data, save a separate copy of the workbook. Macro actions cannot be undone with Ctrl+Z, so a backup is the practical way to recover from an unintended change. Test the form using sample data first, particularly if it writes to cells, clears ranges, or updates other files.

On Windows, Ctrl+Z is the usual Undo shortcut; on Mac, use Command+Z for ordinary worksheet changes. Neither shortcut reverses VBA macro actions. Excel for Mac can run VBA, but interface details and available controls can differ from Windows, and ActiveX controls are unavailable on Mac. Excel for the web does not run VBA macros.

Frequently Asked Questions

What is the simplest VBA code to show a UserForm?
Use UserForm1.Show in a procedure, replacing UserForm1 with the form’s actual name. It opens modally by default.

How do I show a UserForm without blocking Excel?
Call UserForm1.Show vbModeless. The user can then interact with Excel while the form remains open, so make sure the form handles changes to the workbook or active selection appropriately.

How do I close a UserForm with a button?
In the button’s Click event, use Unload Me to close and unload the current form, or Me.Hide to make it invisible while keeping its instance and values.

Can I show a UserForm 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 a desktop version of Excel.

Why does my UserForm button not run the macro?
Check the assigned macro name, confirm that the macro is public and located in a standard module, and verify that macros are permitted for the workbook. For event-driven ActiveX buttons, check the control’s event code and platform support.

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.