Excel VBA UserForm Code Examples and Setup

Excel VBA UserForms are custom dialog boxes for collecting information or guiding a task; useful examples include opening a form, filling a dropdown, checking required fields, and saving entries to a worksheet. In Excel for Microsoft 365 on Windows, create a UserForm in the VBA editor, add named controls, and place each event procedure in the form’s code module.

What a VBA UserForm Does

A UserForm is a small interface built into an Excel workbook. Instead of asking someone to type directly into cells, you can present text boxes, dropdown lists, checkboxes, buttons, and labels. VBA responds to actions such as clicking Save or opening the form, then reads or updates the controls.

A typical form might collect a person’s name, email address, department, and active status, then write those values to a table. The form does not automatically validate or store information: you define that behavior in its event code. A UserForm is also different from a worksheet form control. Worksheet controls sit on a sheet; a UserForm appears as its own window.

The examples below use a form named frmEntry and a worksheet named Records. They assume the worksheet has headings in row 1: Name, Email, Department, and Active. Save a copy of the workbook before running any macro: VBA actions generally cannot be reversed with Ctrl+Z.

Create the Form and Name Its Controls

In desktop Excel for Windows, open the VBA editor with Alt+F11. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer. In the editor, choose Insert > UserForm. Select the form and set its (Name) property to frmEntry; its Caption property controls the text shown in the form’s title bar.

Use the Toolbox to add controls, then set each control’s (Name) property in the Properties window. Names matter because VBA uses them to refer to the controls. For the examples, add:

A TextBox named txtName and another named txtEmail.
A ComboBox named cboDepartment.
A CheckBox named chkActive, with a Caption such as Active.
A CommandButton named cmdSave, with a Caption such as Save.
A second CommandButton named cmdCancel, with a Caption such as Cancel.

Labels are optional, but they make the form easier to understand. A control’s visible Caption is not necessarily its VBA name: for example, the button can display Save while its (Name) is cmdSave. Double-click a control in the form designer to create or open its event procedure.

Show the UserForm from a Worksheet or Button

Put this short procedure in a standard module, not in the UserForm’s code module. In the VBA editor, choose Insert > Module, then add the macro. It displays the form modally by default, so the user must close the form before returning to the workbook.

Public Sub ShowEntryForm()
    ' Open the data-entry form.
    frmEntry.Show
End Sub

Run ShowEntryForm from the Macros dialog or assign it to a worksheet button. To add a button, use Developer > Insert and choose a Button under Form Controls, then assign the macro. A UserForm can also be shown from another VBA procedure. Use frmEntry.Show vbModeless only when users should be able to interact with the worksheet while the form remains open; modeless forms can make it easier to change the sheet while entering data, but require extra care if the active sheet or selection matters.

Fill a Dropdown When the Form Opens

The UserForm_Initialize event runs when the form is loaded. It is a convenient place to prepare a list before the user sees the form. Put this event procedure in the frmEntry code module:

Private Sub UserForm_Initialize()
    Dim departments As Variant
    Dim item As Variant

    ' Supply the choices shown in the department dropdown.
    departments = Array("Finance", "Operations", "Sales", "Support")
    Me.cboDepartment.Clear

    For Each item In departments
        Me.cboDepartment.AddItem CStr(item)
    Next item

    Me.chkActive.Value = True
End Sub

The list is fixed in the code, which is suitable for a short, stable set of choices. If departments change often, keep them in a worksheet range and load that range instead; then a workbook editor can update the choices without editing VBA. Clear the ComboBox before adding items so repeated loading does not duplicate the list. Setting the checkbox to True establishes a default, but users can change it before saving.

Validate the Input and Save a Row

Place this procedure in the form’s code module. It checks that the name and department are present, finds the next row based on column A, and writes the values to the four columns. The email field is saved as entered; this simple example does not verify that an address is valid.

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim personName As String
    Dim emailAddress As String
    Dim department As String

    ' Read and trim text to avoid saving surrounding spaces.
    personName = Trim$(Me.txtName.Value)
    emailAddress = Trim$(Me.txtEmail.Value)
    department = Trim$(Me.cboDepartment.Value)

    If Len(personName) = 0 Then
        MsgBox "Enter a name before saving.", vbExclamation
        Me.txtName.SetFocus
        Exit Sub
    End If

    If Len(department) = 0 Then
        MsgBox "Choose a department before saving.", vbExclamation
        Me.cboDepartment.SetFocus
        Exit Sub
    End If

    On Error GoTo SaveError
    Set ws = ThisWorkbook.Worksheets("Records")

    ' Row 1 contains headings; column A identifies the last used record.
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(nextRow, 1).Value = personName
    ws.Cells(nextRow, 2).Value = emailAddress
    ws.Cells(nextRow, 3).Value = department
    ws.Cells(nextRow, 4).Value = Me.chkActive.Value

    MsgBox "The record was saved.", vbInformation
    Unload Me
    Exit Sub

SaveError:
    MsgBox "The record could not be saved: " & Err.Description, vbCritical
End Sub

For example, if the headings are in row 1 and the last existing name is in A7, the next entry goes to row 8. The code looks for the worksheet in the workbook containing the VBA project because it uses ThisWorkbook; that is safer than relying on whichever workbook happens to be active. Create the Records sheet and its headings before using this example. If column A can be blank for a valid record, choose a different always-filled column to find the next row.

Add Cancel Behavior and Understand Form Events

A Cancel button can close the form without writing a row. Add this event procedure to the same form module:

Private Sub cmdCancel_Click()
    ' Close the form without saving its current entries.
    Unload Me
End Sub

Unload Me closes the form and removes it from memory; showing it again will initialize it again. Me.Hide instead makes the form invisible while keeping it loaded, which can be useful if another procedure will inspect its values before unloading it. Do not hide a form and assume its unsaved entries have been discarded.

Other useful events include UserForm_Initialize for setup and a control’s _Change event for responding as its value changes. Keep event procedures focused: validate a field when necessary, but put longer calculations or repeated tasks in separate procedures. That makes the code easier to read and reduces the chance that an unrelated button event changes the wrong data.

Common Problems and How to Fix Them

“Object required” or “Method or data member not found”: Confirm the form and control names in the Properties window. A caption such as Save does not substitute for the control name cmdSave.
“Subscript out of range”: Check that a worksheet named exactly Records exists in the workbook that contains the code. Worksheet names are not case-sensitive, but spelling and spaces must match.
The button does nothing: Make sure the procedure is in the UserForm module and named for the control, such as cmdSave_Click. Code in a standard module will not automatically run as a button-click event.
Entries overwrite or skip rows: The example uses column A to identify the last record. Check that headings are in row 1 and that every saved record has a name in column A. A blank or interrupted data column can make “last row” logic unsuitable for that layout.
VBA stops on a line: In the editor, use Debug > Compile VBAProject to find compile errors. Add Option Explicit at the top of modules to catch misspelled or undeclared variable names, and verify that each control’s type supports the property or method used.

When debugging, step through a procedure with F8 and inspect values in the Locals window or with the Immediate window. This helps distinguish a naming problem from a data or worksheet problem. Avoid suppressing every error with On Error Resume Next: it can hide failures and leave a form appearing to save when it has not.

Save, Run, and Share the Workbook Safely

Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm). A regular .xlsx file cannot store VBA code; saving to that format removes the macro project. To run the form, macros must be permitted by the user’s Excel security settings. Do not tell users to enable all macros globally. They should follow their organization’s policy, use trusted files, and inspect code from sources they do not know.

Excel for the web can open an .xlsm workbook, but it does not run VBA macros or display VBA UserForms. Users who need the form must open the workbook in desktop Excel. Excel for Mac supports VBA, but its VBA environment and available controls differ from Windows; ActiveX controls are not supported on Mac. Test a form on the platform where it will be used, especially if the project uses Windows-specific controls or automation. Keyboard access to the VBA editor can also differ by Mac keyboard and system settings; use Excel’s Developer tab and Visual Basic command when a shortcut does not work.

When a UserForm Is the Right Choice

A UserForm is useful when a workbook needs a repeatable, guided data-entry process, particularly when users should not edit the underlying table directly. It can enforce required fields, show controlled choices, and keep the worksheet layout uncluttered. It is not automatically the best tool for every workbook, though. A formatted Excel Table with data validation may be simpler to maintain for a small list, while Microsoft Forms or another shared data-collection tool may suit a process that needs browser-based responses from many people.

Consider who will use the workbook, which Excel platforms they have, and how much maintenance the form requires. A VBA interface can become harder to support when several people need simultaneous access, when the list of fields changes frequently, or when users work mainly in Excel for the web. Keep the worksheet data in a clear table even when a form is used; separating the input interface from a well-structured data store makes later filtering, reporting, and troubleshooting easier.

Frequently Asked Questions

Where do I put Excel VBA UserForm code?
Put control event procedures, such as cmdSave_Click, in that UserForm’s code module. Put a macro that opens the form, such as ShowEntryForm, in a standard module.

How do I open a UserForm in Excel?
Run a standard-module macro containing frmEntry.Show, or assign that macro to a worksheet button. The form name in the code must match its (Name) property.

Can a UserForm save data to a worksheet?
Yes. Read control values in a button’s click event and assign them to worksheet cells, as in the Save example. Decide how to identify the destination sheet and next row, and check required fields before writing.

Can Excel for the web run a VBA UserForm?
No. Excel for the web does not run VBA macros or show UserForms. Open the macro-enabled workbook in desktop Excel to use its VBA interface.

Why does my UserForm code say a control is not defined?
Check that the control exists on the form and that its (Name) property matches the code exactly. Also check that the event procedure is in the form module, rather than a standard module.

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.