Excel VBA UserForm Dialogs: How to Build and Show One

VBA & macros · Updated November 21, 2026

An Excel VBA UserForm is a custom dialog you design to collect information, display choices, or guide a user through a task. In Excel for Microsoft 365 on Windows, create one in the Visual Basic Editor with Insert > UserForm, add controls such as text boxes and buttons, then show it with a VBA statement such as frmCustomer.Show vbModal.

What a UserForm Is—and When to Use One

A UserForm is a small window built into a workbook’s VBA project. Unlike a worksheet form made from cells, it can appear independently of the sheet and combine labels, text boxes, buttons, check boxes, drop-down lists, and other controls. Its appearance and behavior are set in the Visual Basic Editor, and its events can run VBA code.

UserForms are useful when a macro needs information in a predictable format. For example, a form can ask for a customer name and order date, let someone choose a department from a list, or provide a guided set of options before a report is created. The macro can validate entries before writing them to a worksheet, which is often clearer than asking a user to type directly into cells.

A UserForm is not automatically a complete data-entry system. You decide what each control does, where its values go, and how errors or cancellations are handled. A form also does not make a workbook safe or secure by itself; workbook protection and data validation are separate concerns.

Create a Basic UserForm in Excel

On Windows, open the workbook and press Alt+F11 to open the Visual Basic Editor. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. In the editor, select the workbook project in Project Explorer, then choose Insert > UserForm. Excel adds a blank form and normally displays the Toolbox, which contains controls you can place on it.

  1. Select the form and use the Properties window to set its (Name), such as frmCustomer, and its Caption, such as Customer details. The name is used in VBA; the caption is the text shown in the form’s title bar.
  2. Choose a control in the Toolbox and draw it on the form. Add a Label to explain the field, a TextBox for an entry, and CommandButtons for actions.
  3. Set useful control names in the Properties window. For example, name a text box txtName, an OK button cmdOK, and a Cancel button cmdCancel.
  4. Double-click a button to open its click-event procedure, then add the code that should run when the user selects it.

The visual layout is only one part of the work. Each control should have a clear purpose, a meaningful name, and an event procedure where needed. A button named CommandButton1 works, but a descriptive name makes later edits easier to understand.

Show the Dialog and Handle Its Buttons

For a simple modal form, the calling procedure can show the dialog, wait for the user, read the result, and then unload the form. In this example, first create a UserForm named frmCustomer with a TextBox named txtName and two buttons named cmdOK and cmdCancel. Put the first code block in the UserForm’s code module:

Option Explicit

Public WasCancelled As Boolean

Private Sub UserForm_Initialize()
    ' Treat closing the form as cancellation unless OK is selected.
    WasCancelled = True
End Sub

Private Sub cmdOK_Click()
    WasCancelled = False
    Me.Hide
End Sub

Private Sub cmdCancel_Click()
    WasCancelled = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' Keep the form available so the calling procedure can read its result.
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        WasCancelled = True
        Me.Hide
    End If
End Sub

Put this second procedure in a standard module. Before running any macro, save a copy of the workbook: VBA macros cannot be undone with Ctrl+Z.

Option Explicit

Public Sub AskForCustomer()
    Dim dlg As frmCustomer
    Dim customerName As String

    Set dlg = New frmCustomer
    dlg.Show vbModal

    If Not dlg.WasCancelled Then
        customerName = Trim$(CStr(dlg.txtName.Value))

        If Len(customerName) > 0 Then
            MsgBox "Customer entered: " & customerName, vbInformation
        Else
            MsgBox "Enter a name before continuing.", vbExclamation
        End If
    End If

    Unload dlg
    Set dlg = Nothing
End Sub

The vbModal argument means the user must close or dismiss the form before interacting with the workbook window behind it. The form hides when a button is selected, leaving its controls available for the calling procedure to read. The close-button event also treats the title-bar X as cancellation. This example reports an empty entry but does not reopen the form; a production workflow could instead validate the text in the button event and keep the form open until it is acceptable.

Modal and Modeless Forms

Modal is the usual choice for a short question or required entry because it keeps the user focused on the dialog. With vbModal, the calling procedure pauses at the Show line until the form is hidden or unloaded. That makes the sequence—ask, check the response, continue—straightforward.

A modeless form is shown with vbModeless. It stays open while the user can also work in Excel, which can help with a utility panel or a form used alongside a worksheet. However, the macro does not wait at the Show line for the user to finish. Code that expects a response immediately after showing a modeless form will therefore need a different design, usually with the form’s own event procedures handling later actions.

Modeless forms also need careful lifecycle management. Provide a clear way to close the form, avoid opening duplicate instances accidentally, and consider whether changes in the workbook could make the form’s displayed information stale.

Choosing the Right Dialog Tool

A UserForm is flexible, but it is not always necessary. Choose the simplest tool that fits the interaction:

  • MsgBox: Use it for a short message or a basic Yes/No decision. It is quick to add, but its layout and input controls are limited.
  • InputBox: Use it for one simple value when a custom layout, richer validation, or several fields are not required. The returned text still needs to be checked if the macro depends on a particular format.
  • UserForm: Use it when the task benefits from multiple controls, clear instructions, validation, or a repeatable custom layout.
  • Worksheet cells or a table: Use these when users need to enter or review many records, sort data, or work collaboratively with visible data. A form may still be useful as an optional front end.

Excel also exposes built-in dialogs through VBA in some cases, but they do not provide the same freedom as a custom form. Before building a UserForm, consider whether an ordinary worksheet, a table with data validation, or a short built-in prompt would be easier for the next person to maintain.

Validation, Accessibility, and Usability

Do not assume that a user has entered valid information merely because a text box contains something. Check required fields, data type, allowed values, and relevant limits before the macro changes the workbook. For a date, for instance, parse and validate the entry rather than relying only on how it looks. For a choice that must come from a fixed set, a ComboBox or ListBox is generally less error-prone than free-form typing.

Make the form understandable without relying on color alone. Use labels that describe the expected input, keep related controls together, and make button captions explicit. A tab order that follows the visual reading order helps keyboard users move through fields. Test the form at the size and display scaling used by its intended audience; controls can become crowded if a form is designed too tightly.

Keep the form’s work focused. A dialog that asks only for the information needed for one task is easier to use than a crowded window with unrelated options. If the form changes or deletes worksheet data, describe the action clearly and consider confirmation where the consequence is significant.

Saving, Running, and Sharing the Workbook

Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) or another format that preserves its VBA project. Saving it as a standard .xlsx file removes the VBA code. If Excel displays a security warning, macros may be disabled by the user’s Trust Center settings or organizational policy; users should enable code only when they trust the workbook’s source.

To run a public macro such as AskForCustomer, use Developer > Macros, select its name, and choose Run. You can also assign a macro to a worksheet button or another suitable control. A control on the UserForm itself uses its event procedure instead; it is not the same as assigning a standard macro to a worksheet button.

For shared workbooks, test the complete file on a copy and with macros enabled. Check that the form opens, each button behaves as expected, invalid values are handled, and cancellation leaves the workbook in a sensible state. A UserForm is part of the VBA project, so distributing the workbook also distributes the form design and its code.

Windows, Mac, and Excel for the Web

This guidance uses Excel for Microsoft 365 on Windows. The Visual Basic Editor on Windows is commonly opened with Alt+F11; on many Mac keyboards, use Option+F11, sometimes with the Fn key depending on keyboard settings. Excel for Mac has a VBA editor and can work with VBA, but control availability and behavior can differ from Windows, so test any UserForm and its controls on the target Mac version rather than assuming an identical interface.

Excel for the web does not create, edit, or run VBA macros. A workbook with VBA can be stored and opened there, but its VBA automation will not execute in the browser. If browser-based access is a requirement, consider Office Scripts or a worksheet-based workflow instead, checking that the alternative supports the needed behavior.

Common Problems and Fixes

  • The UserForm does not appear: Confirm that the macro is running, that the form’s (Name) matches the name used in code, and that macros are enabled for a trusted workbook.
  • “Variable not defined” or “Method or data member not found” appears: Check spelling and make sure the control’s actual (Name), not its Caption, matches the code. Compile or step through the VBA code to locate the issue.
  • The form closes but the caller gets no value: Decide whether to use Hide to preserve control values until they are read or Unload to remove the form from memory. Unloading too early can discard values or reset the form when it is loaded again.
  • The code runs twice or opens extra forms: Check for multiple event procedures or multiple calls to the display macro. If a modeless form is used, manage its instances deliberately.
  • A control is missing or behaves differently on Mac: Review platform support and replace platform-specific controls where necessary. Test on each operating system the workbook is expected to support.

Frequently Asked Questions

How do I open a VBA UserForm in Excel?
Call its name followed by .Show from a VBA procedure, such as frmCustomer.Show vbModal. Use the form’s actual (Name) property, not its window caption.

How do I add a button to a UserForm?
Open the form in the Visual Basic Editor, select CommandButton in the Toolbox, and draw it on the form. Change its (Name) and Caption in Properties, then add its behavior in the button’s Click event procedure.

What is the difference between Show and Show vbModeless?
Show vbModal prevents interaction with Excel until the dialog is dismissed and pauses the calling procedure. Show vbModeless leaves Excel available and lets the calling procedure continue.

Can I use a VBA UserForm in Excel for the web?
No. Excel for the web does not run VBA macros or display VBA UserForms as interactive dialogs. Use desktop Excel for the VBA workflow or consider a web-compatible alternative.

Why does my UserForm disappear when I try to read its text box?
Check whether your code unloads the form before reading the control. Hiding the form preserves the instance and its values while the calling code reads them; unload it afterward when finished.

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.