In Excel VBA, display a UserForm by calling its Show method, such as frmEntry.Show. By default, the form is modal; add vbModeless to let users work in Excel while it remains open.
What a UserForm does
A UserForm is a custom dialog window in an Excel workbook. It can collect input, present choices, or guide someone through a task without requiring them to type directly into worksheet cells. Common controls include text boxes, labels, buttons, check boxes, and list boxes. A form can also contain VBA code that responds to actions such as clicking a button or changing a field.
Showing a form is separate from creating it. First, add a UserForm in the Visual Basic Editor, set its properties, and place controls on it. Then call Show from a procedure, a worksheet event, a workbook event, or another form. The form’s object name, not its displayed caption, is used in VBA code.
Create and name the form
In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > UserForm. The new form appears in the project and its design surface opens. If the Properties window is hidden, press F4 to show it.
Select the form and change its (Name) property to a useful identifier, for example frmEntry. The Caption property controls the words displayed in the form’s title bar; it might say “Add a record” even though the form’s code name is frmEntry. Add controls from the Toolbox and set their names, such as txtName for a text box or cmdSave for a button. Clear, consistent names make event code easier to understand.
On a Mac, Excel also includes the Visual Basic Editor and UserForms, though available controls and behavior can differ from Windows. Depending on the keyboard, open the editor with Option+F11; some keyboards require holding Fn as well. Menu locations and keyboard behavior can vary across Mac versions, so use Excel’s Tools > Macro > Visual Basic Editor menu if the shortcut does not work.
Display a form with Show
For the simplest case, put a short procedure in a standard module. In the Visual Basic Editor, choose Insert > Module, then use the form’s name followed by .Show. This example assumes the UserForm is named frmEntry:
Public Sub OpenEntryForm()
' Display the form and wait until it is dismissed.
frmEntry.Show vbModal
End Sub
Because vbModal is the default, frmEntry.Show has the same modal behavior. Writing the constant explicitly can make the intended interaction clearer to someone reading the procedure. To run the macro from the Visual Basic Editor, place the cursor inside the procedure and press F5. You can also run a public macro from Excel’s Developer > Macros command or assign it to a worksheet button.
Before running a macro that changes workbook data, save a copy of the workbook. VBA actions cannot be reversed with Ctrl+Z, so a backup is a practical safeguard.
Choose modal or modeless behavior
A modal form keeps the user in the dialog until it is hidden or unloaded. In Excel, the user cannot interact with the workbook window while that modal form is open. This is useful when the form needs an answer before a macro can safely continue, such as choosing a required option before creating a report.
A modeless form stays open while the user can also work in the workbook. To show one, use vbModeless:
Public Sub OpenToolsForm()
' Leave Excel available while the form is open.
frmTools.Show vbModeless
End Sub
This is useful for a small search panel or a tool that repeatedly acts on the current selection. It also requires care: the active sheet, selected cell, or workbook data may change while the form is visible. Code should check that its target still exists and is appropriate before writing to it. A modeless form should provide a clear way to close or hide it, because it does not automatically block other Excel actions.
Use a new form instance when needed
Referring to frmEntry directly uses the form’s default instance. This is convenient for a small macro, but it can be less obvious when a form’s values need to be managed across several procedures. You can create an explicit instance instead. The following example assumes the UserForm’s name is frmEntry:
Public Sub OpenFreshEntryForm()
Dim entryDialog As frmEntry
' Create a separate instance for this display.
Set entryDialog = New frmEntry
entryDialog.Show vbModal
Set entryDialog = Nothing
End Sub
An explicit instance is especially useful when code needs to refer to that particular form object, or when an application may display more than one instance. A form variable declared inside a procedure goes out of scope when the procedure ends. With a modal display, the call waits until the form closes, so cleanup after Show is straightforward. With a modeless display, the procedure continues immediately; do not discard the only reference if later code needs to control that instance. Store it in a suitable module-level variable when its lifetime must extend beyond the launching procedure.
Initialize, hide, and unload the form
When a form is first loaded, its UserForm_Initialize event is a common place to set initial control values. For example, code in the form’s own code module might set a label caption, clear a text box, or fill a list. The UserForm_Activate event occurs when the form becomes active; it can run again if the form is reactivated, so it is not always the right place for one-time setup.
To close a form from one of its buttons, add code to that button’s click event. If the button is named cmdClose, its event procedure is cmdClose_Click:
Private Sub cmdClose_Click()
' Remove this form from memory.
Unload Me
End Sub
Me refers to the current form. Use Unload Me when the form should be removed and its controls’ current values should not be relied on afterward. Use Me.Hide when you want it to disappear but preserve the loaded form and its current control values for later use. That distinction matters in a multi-step workflow: hiding can retain input, while unloading and showing the default instance again generally requires initialization and fresh entry.
Clicking the title-bar close button is another way a user can dismiss a form. If closing must be controlled—for example, to ask whether to discard unsaved entries—handle the form’s UserForm_QueryClose event and validate the close request. Avoid trapping users in a form with no usable way out.
Open a form from a worksheet control
A common pattern is to create a public macro in a standard module and assign it to a button on a worksheet. In Excel, use Developer > Insert, choose a Button under Form Controls, draw it on the sheet, and select the macro when prompted. The macro can then call frmEntry.Show. A Form Control button is often simpler for a basic launch action than adding click logic to a worksheet object.
You can also open a form from an event procedure, such as a workbook or worksheet event, but automatic opening can surprise users and can create loops if the event is triggered by code that edits cells. Keep event procedures narrow, and use event-control techniques only when there is a clear reason to launch the form automatically. For a workbook that should open with a dialog, a workbook-open event can call a dedicated public procedure, but test the behavior with macros enabled and provide a way to reach the workbook if the form is dismissed.
Common display problems and fixes
- “Variable not defined” or “Subscript out of range”: Check the form’s
(Name)property and spelling. The caption is not the identifier. If the form is calledUserForm1,frmEntry.Showwill not work until the form is actually renamed or the code uses its real name. - The macro is missing from the Macros list: A macro intended to be run directly should generally be a
Public Subin a standard module and should not require arguments. Event procedures and private procedures are not ordinary macro-list entries. - The form does not appear: Confirm that macros are enabled for a trusted workbook, that the procedure runs, and that the form is not being immediately hidden or unloaded by its own code. Use a breakpoint or step through the procedure in the Visual Basic Editor to locate where execution goes.
- A modeless form interferes with later work: Check whether code assumes the same sheet or selection remains active. Capture a specific workbook, worksheet, or range reference when appropriate, and validate it before acting. Avoid relying on whichever cell happens to be selected after the user interacts with Excel.
- The form opens in an awkward position: Review properties such as
StartUpPositionand test on the screens and display scaling used by the people who will run the workbook. Large or multi-monitor setups can affect where a dialog appears.
Security and workbook compatibility
VBA runs only when the workbook is saved in a macro-enabled format, such as .xlsm, and the user permits its macros to run. Saving a macro-enabled workbook as .xlsx removes its VBA project. Do not tell users to enable all macros globally; follow organizational policy and use trusted files from known sources. A UserForm is an interface, not a security boundary: users who can access or inspect the workbook may still be able to view or change workbook content and VBA code.
UserForms are a desktop Excel feature, not a way to make VBA run in Excel for the web. The web app does not run VBA macros. If a workbook must work in a browser, use a web-compatible approach such as Office Scripts where suitable, or design the workflow around ordinary worksheet controls and supported features. Macros and UserForms should also be checked on each target desktop platform because interface details and supported controls are not perfectly identical.
Frequently Asked Questions
What is the shortest code to display a VBA UserForm?
Use frmEntry.Show, replacing frmEntry with the form’s actual (Name). The default display is modal.
How do I show a UserForm without blocking Excel?
Call frmEntry.Show vbModeless. Excel remains available, so the form’s code should account for changes to the workbook while it is open.
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. Use Me.Hide instead if you want to keep it loaded with its current values.
Can a UserForm open automatically when a workbook opens?
Yes. A workbook-open event can call a procedure that displays the form. Make sure the workbook still offers a clear way to close the dialog and access the sheets.
Can I display a VBA UserForm in Excel for the web?
No. Excel for the web does not run VBA macros or display VBA UserForms. Use desktop Excel for that workflow or choose an approach designed for browser use.
Related
- Power Query Text to Columns: Split Data Step by Step
- Data Validation Framework: Design, Rules, and Examples
- What Is Power Query? Excel Data Cleanup Explained
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel UserForm Initialize: How It Works and How to Use ItIn Excel VBA, UserForm_Initialize is the event procedure that runs when a UserForm instance is loaded, before the…
- VBA Input Box With Multiple Inputs: Practical OptionsExcel’s VBA InputBox collects one response per prompt, not several separate fields in a single dialog. To gather…
- Excel Macro Programming: What It Is and How It WorksExcel macro programming means creating instructions that automate tasks in a workbook, most commonly with Visual Basic for…
- Excel VBA Convert Date to Number: Serial ValuesYes. Excel dates are stored as serial numbers, so VBA can read a date cell’s underlying number with…
- VBA Find in a String: InStr, Examples, and OptionsTo find text inside a VBA string, use the InStr function. It returns the position of the first…
- Replace Excel Macros? Alternatives and When to SwitchYes—many Excel macros can be replaced, but the right substitute depends on the task: use formulas for calculations,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.