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.
- Select the form and use the Properties window to set its (Name), such as
frmCustomer, and its Caption, such asCustomer details. The name is used in VBA; the caption is the text shown in the form’s title bar. - 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.
- Set useful control names in the Properties window. For example, name a text box
txtName, an OK buttoncmdOK, and a Cancel buttoncmdCancel. - 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
Hideto preserve control values until they are read orUnloadto 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
- Excel Text to Columns: Advanced Options and Uses
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns: How to Make Results Vertical
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Sort Order in Excel: Rules and ExamplesIn Excel VBA, sort order is set with xlAscending or xlDescending; the sort key determines which column or…
- Excel VBA Date and Time Functions: A Practical GuideExcel VBA’s main current-date and time functions are Date, Time, and Now; use DateSerial and TimeSerial to build…
- Autofit VBA Range: Columns, Rows, and ExamplesIn Excel VBA, use the AutoFit method to size worksheet columns or rows to fit their contents. For…
- Excel VBA Subscript Out of Range: Causes and FixesExcel VBA “Run-time error 9: Subscript out of range” means your code asked for an item that does…
- Excel VBA Input Box Date Picker: What Actually WorksExcel’s VBA InputBox does not include a calendar or date-picker control: it asks the user to type a…
- Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.