VBA UserForm Guide: Build, Code, and Run Excel Forms

A VBA UserForm is a custom dialog you build in Excel to collect information or guide a user through a task. To make one, open the Visual Basic Editor, choose Insert > UserForm, add controls from the Toolbox, write event code in the form’s code module, then show it from a macro. This guide covers the practical setup, a working example, and the choices and pitfalls to understand before using a form.

What a VBA UserForm Does

A UserForm is a custom interface created with Excel’s Visual Basic for Applications (VBA). It can present text boxes, buttons, labels, lists, check boxes, and other controls without asking users to type directly into worksheet cells. For example, a small entry form could ask for an item name and quantity, check that both are valid, and add the values to a table.

A form is part of the workbook’s VBA project, not a worksheet feature that runs independently. Its controls have properties such as Name, Caption, and Value, and its code responds to events such as clicking a button or opening the form. Excel for Microsoft 365 on Windows is the focus here. Excel for the web cannot run VBA macros, and UserForm support and available controls can differ on Mac, so test a workbook on the platform where it will be used.

Create the Form and Set Its Properties

Save a working copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm). VBA can also be stored in other macro-capable formats, but .xlsm is the usual choice for a workbook with macros.
Open the Visual Basic Editor with Alt+F11 on Windows. On a Mac, use Tools > Macro > Visual Basic Editor; the keyboard shortcut can vary by keyboard and Excel setup.
In the editor, choose Insert > UserForm. If the Toolbox is not visible, open it from the View menu. Drag controls onto the form and resize or reposition them as needed.
Select each control and use the Properties window, usually opened with F4, to set its (Name) and display properties. For the example below, name two text boxes txtName and txtQty, and two command buttons cmdSave and cmdCancel. Set the buttons’ Caption values to Save and Cancel.

Use descriptive control names rather than leaving defaults such as TextBox1. The Name is what VBA code refers to; Caption is the text displayed to the user. A label beside each text box should explain what belongs there. Set the form’s own (Name) property to frmEntry and its Caption to something clear, such as Add an entry. A predictable naming scheme makes later maintenance easier.

Understand Controls, Properties, and Events

Controls provide the visible parts of the interface, while their properties describe how they look or behave. A TextBox accepts typed content; a Label displays instructions; a CommandButton triggers an action. A ComboBox can offer a drop-down list, and a CheckBox represents a yes-or-no choice. Choose controls based on the data and interaction you actually need rather than adding decoration that makes the form harder to use.

VBA code usually runs in response to an event. For instance, the procedure cmdSave_Click runs when the user clicks the Save button, and UserForm_Initialize runs as the form is initialized. In the Visual Basic Editor, double-clicking a control opens its code window and commonly creates its default event procedure. The form’s code module is the right place for procedures that operate on its controls; a standard module is a suitable place for a macro that opens the form.

Build a Simple Data-Entry Example

Assume the worksheet named Entries exists and has headers in cells A1 and B1, for example Item and Quantity. The form has the controls named above. Put this procedure in the frmEntry code module. It rejects a blank name and a missing, nonnumeric, or nonpositive quantity before writing a new row:

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim itemName As String
    Dim quantity As Double

    ' Read and validate the entries.
    itemName = Trim$(Me.txtName.Value)
    If Len(itemName) = 0 Then
        MsgBox "Enter an item name.", vbExclamation
        Me.txtName.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.txtQty.Value) Then
        MsgBox "Enter a numeric quantity.", vbExclamation
        Me.txtQty.SetFocus
        Exit Sub
    End If

    quantity = CDbl(Me.txtQty.Value)
    If quantity <= 0 Then
        MsgBox "Quantity must be greater than zero.", vbExclamation
        Me.txtQty.SetFocus
        Exit Sub
    End If

    ' Append the values below the existing entries.
    Set ws = ThisWorkbook.Worksheets("Entries")
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ws.Cells(nextRow, "A").Value = itemName
    ws.Cells(nextRow, "B").Value = quantity

    ' Clear the form ready for another entry.
    Me.txtName.Value = vbNullString
    Me.txtQty.Value = "1"
    Me.txtName.SetFocus
End Sub

The row calculation looks upward from the bottom of column A to find the last used cell, then adds one. Because the example assumes headers in row 1, the first saved item goes into row 2. It uses ThisWorkbook so the destination is the workbook containing the VBA code. If the destination sheet is renamed or removed, the procedure will fail; make sure the sheet name matches exactly. IsNumeric accepts more than just whole numbers, so add a separate whole-number check if quantities must be integers.

Initialize, Open, and Close the Form

You can set useful starting values when the form opens. Add this event procedure to the form’s code module:

Private Sub UserForm_Initialize()
    ' Start with an empty name and a default quantity.
    Me.txtName.Value = vbNullString
    Me.txtQty.Value = "1"
End Sub

To add a Cancel button that closes the form without saving, use this procedure:

Private Sub cmdCancel_Click()
    ' Close the form and release its loaded instance.
    Unload Me
End Sub

To display the form, insert a standard module with Insert > Module and add a public macro:

Public Sub ShowEntryForm()
    ' Display the form as a modal dialog.
    frmEntry.Show
End Sub

Run ShowEntryForm from the Visual Basic Editor or assign it to a worksheet button. A form shown this way is modal by default: the user must close it before working in the workbook window. You can use frmEntry.Show vbModeless when it makes sense for users to interact with the sheet while the form remains open, but modeless forms require extra care because worksheet selection or data may change while the form is displayed. Unload Me closes and removes the form instance; Hide makes it invisible but keeps it loaded, which can preserve its current values.

Validation, Usability, and Data Handling

Validation should prevent incorrect records, not merely display a warning after bad data has been written. Check required fields, numeric ranges, date formats, and permitted choices before saving. For fields with a fixed set of allowed values, a ComboBox can reduce typing errors. Give each field a clear label, use a logical tab order, and keep button captions specific. A form that is usable with a keyboard is easier for many people to navigate.

Consider how the form will behave after a successful save. The example clears the fields but stays open for another entry. If the intended task is a single submission, unload the form after saving instead. For a real workbook, it is also wise to plan what should happen if the destination worksheet is protected, a required sheet is missing, or the workbook is opened read-only. For larger projects, writing to an Excel table rather than relying on a fixed range can make the stored data easier to filter and extend.

Common Problems and How to Diagnose Them

The form does not appear: Confirm that the launch macro is in a standard module, that its name matches the macro you run, and that macros are permitted for this trusted file. Check that the form’s (Name) property is frmEntry if the launch code refers to frmEntry.
“Variable not defined” or “Method or data member not found” appears: Check spelling and the control’s (Name) property. A control named txtQuantity is not the same as txtQty. Also make sure the procedure is in the correct form module.
The save code cannot find the worksheet: Confirm that a worksheet named Entries exists in the workbook containing the code. Worksheet tab text and the name used in the VBA reference must match.
Values go into the wrong place: Verify the destination columns and headers assumed by the code. The sample uses column A to find the last row and writes the name and quantity to columns A and B.
The Toolbox or Properties window is missing: Use the Visual Basic Editor’s View menu to show the relevant window. Selecting the form or a control changes which properties appear.

When debugging, use the editor’s Debug menu to compile the VBA project and step through code, or add a breakpoint by clicking beside a line. A compile check can reveal misspellings and syntax errors, while stepping through can show which condition or worksheet reference causes a problem. Excel for Mac has a VBA editor, but interface details and control support can differ from Windows; verify the exact form on the target computer.

Security and Workbook Maintenance

VBA macros can change workbook data, and Excel may block macros from files it does not trust. Do not enable macros in an unfamiliar workbook just to see what a form does. Only use files from a source you trust, follow your organization’s security rules, and keep a backup before changing or running code. Macro settings and trust policies may be managed by an organization, so users may not be able to override them.

Save a separate copy before running or editing a macro: actions performed by VBA generally cannot be reversed with Ctrl+Z (or Command+Z on Mac). Test with sample data first, then confirm that the records land in the expected worksheet and that invalid entries are rejected. Keep the workbook in a macro-capable format and document the sheet names and required controls so another person can maintain it.

When to Use a UserForm Instead of an Alternative

A UserForm is useful when a workbook needs a tailored desktop interface, repeated data entry, or a sequence of prompts and checks. It is not automatically the best solution for every spreadsheet. If users can enter data directly into a well-structured Excel table, data validation and clear column headings may be simpler to build and maintain. A UserForm also depends on VBA, so it is unsuitable when users must work in Excel for the web or when an organization blocks macros.

For collaborative collection across devices, Microsoft Forms or a Power Apps solution may fit better, depending on where the responses need to go and the organization’s Microsoft 365 setup. These are separate tools, not VBA UserForms embedded in a worksheet. Choose based on where users work, how data is stored, the need for validation, and who will maintain the solution.

Frequently Asked Questions

How do I create a UserForm in Excel? Open the VBA editor, choose Insert > UserForm, add controls from the Toolbox, set their names and properties, and write event procedures in the form’s code module. Save the workbook in a macro-enabled format and run a macro that shows the form.

Can I use an Excel VBA UserForm in Excel for the web? No. Excel for the web does not run VBA macros, so a VBA UserForm will not work there. Use desktop Excel for a VBA-based form or consider a web-oriented alternative for browser-based data collection.

What is the difference between Hide and Unload? Hide makes a form invisible but leaves its instance loaded, so its current values can remain available. Unload closes and removes that instance; showing the form again creates a fresh one and initializes it again.

Why does my UserForm code say a control is not found? The code may use a different name from the control’s (Name) property, or the procedure may be in the wrong module. Check the property in the VBA editor and use exactly the same spelling in the code.

Can I add a UserForm to a worksheet button? Yes. Add a button and assign it a public macro in a standard module, such as ShowEntryForm. The macro can then call the form’s Show method. The button and macro both require a workbook environment that permits VBA.

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.