VBA Code for UserForm: Build an Excel Data Entry Form

VBA & macros · Updated November 18, 2026

To create a VBA UserForm in Excel, add a UserForm in the Visual Basic Editor, place controls on it, and write event code that validates the entries and saves them to a worksheet. The example below builds a simple contact form with a name, email address, department, and active-status checkbox; it works in desktop Excel for Microsoft 365 on Windows.

What an Excel UserForm Does

A UserForm is a custom dialog box made with VBA. It can collect information, display choices, and respond to buttons or other events. Instead of asking someone to type directly into cells, a form can guide them through a consistent set of fields and then put the results in a worksheet.

A typical form combines controls such as text boxes, labels, combo boxes, checkboxes, and command buttons. Each control has a name and properties, while VBA procedures determine what happens when the user opens the form, changes a choice, or clicks a button. For example, a Save button can check for required values and write one new record to a table.

UserForms are part of desktop Excel’s VBA environment. They are not the same as Microsoft Forms or the online forms features associated with Excel. Excel for the web can open a workbook containing VBA, but it cannot create or run the workbook’s macros. Users need desktop Excel to use this example.

Prepare the Workbook and Form Controls

Save a working copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm). In Windows Excel, open the Visual Basic Editor with Alt+F11, or choose Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.

In the Visual Basic Editor, choose Insert > UserForm. In the Properties window, change the form’s (Name) property to frmEntry and its Caption to something like New Contact. Add controls from the Toolbox and set their (Name) properties as follows. The visible captions can be adjusted to suit the workbook.

  • TextBox: txtName, for the person’s name.
  • TextBox: txtEmail, for an email address.
  • ComboBox: cboDepartment, for a department selection.
  • CheckBox: chkActive, for an active-status choice.
  • CommandButton: cmdSave, with the Caption set to Save.
  • CommandButton: cmdCancel, with the Caption set to Cancel.

Labels are optional for the code, but they make the form easier to understand. Also add a worksheet named Data. Put the headers Name, Email, Department, and Active in cells A1:D1. The sample writes each new contact to the next available row, using column A to find the last existing record.

VBA Code for the UserForm

Double-click the form background to open its code window. Add the following procedures there. The Initialize event fills the department list when the form opens. The Save event checks required entries before writing values; Cancel closes the form without saving.

Option Explicit

Private Sub UserForm_Initialize()
    ' Load the choices shown in the department combo box.
    With Me.cboDepartment
        .Clear
        .AddItem "Sales"
        .AddItem "Marketing"
        .AddItem "Operations"
        .AddItem "Other"
    End With

    Me.chkActive.Value = True
End Sub

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim nameValue As String
    Dim emailValue As String

    ' Trim leading and trailing spaces from required text.
    nameValue = Trim$(Me.txtName.Value)
    emailValue = Trim$(Me.txtEmail.Value)

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

    If Len(emailValue) = 0 Then
        MsgBox "Enter an email address before saving.", vbExclamation
        Me.txtEmail.SetFocus
        Exit Sub
    End If

    If Me.cboDepartment.ListIndex = -1 Then
        MsgBox "Choose a department before saving.", vbExclamation
        Me.cboDepartment.SetFocus
        Exit Sub
    End If

    ' ThisWorkbook means the workbook that contains this VBA project.
    Set ws = ThisWorkbook.Worksheets("Data")
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = nameValue
    ws.Cells(nextRow, 2).Value = emailValue
    ws.Cells(nextRow, 3).Value = Me.cboDepartment.Value
    ws.Cells(nextRow, 4).Value = Me.chkActive.Value

    MsgBox "The contact was saved.", vbInformation
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    ' Close the form without adding a worksheet row.
    Unload Me
End Sub

The form code assumes the worksheet exists and is named exactly Data. It also assumes column A contains a value for every existing record. The email check only confirms that the field is not blank; it does not verify whether the address is correctly formatted or deliverable.

Open the Form from Excel

To display the form, add a standard module in the Visual Basic Editor with Insert > Module. Put this macro in the module, then run it from the Macros dialog or assign it to a worksheet button.

Option Explicit

Public Sub OpenEntryForm()
    Dim entryForm As frmEntry

    ' Create and display a new form instance.
    Set entryForm = New frmEntry
    entryForm.Show
End Sub

In Windows Excel, Alt+F8 opens the Macro dialog, where you can select OpenEntryForm and choose Run. To connect the macro to a worksheet button, use Developer > Insert > Button (Form Control), draw the button, and select the macro when prompted. A shape can also be assigned a macro through its context menu. On Mac, the Visual Basic Editor and Macro dialog are available in desktop Excel, but the keyboard shortcut can vary with the keyboard and macOS settings; use the Excel menus if Option+F11 or Option+F8 is intercepted by a system function key.

How the Save Process Works

The button’s Click event runs only when the user presses Save. First, Trim$ removes spaces at the beginning and end of the two text values. The Len checks stop the procedure if either required text box is empty. The combo box check uses ListIndex: a value of -1 means that no list item is selected.

After validation, the code refers to the workbook containing the VBA project with ThisWorkbook, then finds the next row by moving upward from the bottom of column A. The four assignments place the entered values in columns A through D. The checkbox value is stored as TRUE or FALSE, so it can later be filtered or used in worksheet formulas. Finally, Unload Me closes the form and releases that form instance.

This is a small example rather than a complete contact-management system. It does not prevent duplicate contacts, check whether a department has been renamed, or create a missing worksheet. Those rules depend on how the workbook is intended to be used, so add them deliberately rather than assuming the basic form handles them.

Customize Validation and Data Storage

For a different form, change the control names, list choices, and destination columns to match the workbook. If you add a control, use its actual (Name) property in the code. A frequent source of compile errors is writing a procedure for cmdSave when the button is still named CommandButton1, or renaming a control in code without changing it in the form designer.

For stronger email validation, a basic pattern check can reject obvious mistakes, but VBA string checks cannot establish that an address exists or belongs to the intended person. If accuracy matters, use an appropriate confirmation process. For dates or numbers, validate the input before writing it, and consider whether the worksheet should store a real Excel date or number rather than text.

If column A might be blank in some records, do not rely on it as the sole way to locate the next row. Choose a column that is always populated, or use a structured Excel table and add a new ListRow. A table can make sorting, filtering, and expanding the data easier, but its headers and column names must match the code you write for it.

Common Errors and Troubleshooting

  • “Sub or Function not defined” or a compile error: Check spelling, control names, and whether code is in the correct form or standard module. In the Visual Basic Editor, use Debug > Compile VBAProject to locate compile-time problems.
  • “Subscript out of range”: The worksheet reference probably does not match a sheet tab name. Confirm that the tab is named exactly Data, including spaces.
  • The form opens but does not save: Check that the Save button is named cmdSave and that the code is in the form’s module. A procedure named cmdSave_Click is associated with that control.
  • New records overwrite or skip data: Verify that column A is populated for each existing record and that the headers are in row 1. If the layout differs, adjust the row-finding logic.
  • Macros are disabled: Save as .xlsm and follow your organization’s approved macro policy. Do not enable macros in a file from an untrusted source just to make a form work.

When debugging, set a breakpoint by clicking beside a code line in the editor, then run the macro and step through the procedure with F8 on Windows. The Mac editor has equivalent debugging commands, though shortcut behavior can differ. Inspect the values in the Locals window or with the Immediate window to see whether validation and row selection behave as expected.

Safety, Compatibility, and Alternatives

Save a separate copy of the workbook before running or changing a macro. VBA changes to worksheet cells cannot be undone with Ctrl+Z, and a macro can make many changes in one run. Keep a backup of important data, and review code before running it. Macro settings are controlled by Excel’s security options and may also be restricted by an organization.

The code targets desktop Excel for Microsoft 365 on Windows. Excel for Mac supports VBA and UserForms, but some controls, editor details, and keyboard shortcuts can differ; test a workbook on the platform where it will be used. Excel for the web does not run VBA macros. A UserForm also is not automatically a multi-user online form: simultaneous users may need a shared data-entry service rather than separate macro-enabled workbook copies.

If the task is simple, direct worksheet entry with data validation may be easier to maintain. For example, a validation list can constrain department values without writing a form. Microsoft Forms can collect responses through a browser and may suit a shared questionnaire, while Office Scripts are a different automation option for Excel on the web and are written in TypeScript rather than VBA. Choose based on whether the workbook needs a desktop dialog, a browser-based collection experience, or straightforward cell entry.

Frequently Asked Questions

Where do I put VBA code for a UserForm?
Put events such as cmdSave_Click in that UserForm’s code module. Put a macro that opens the form, such as OpenEntryForm, in a standard module.

How do I show a UserForm in Excel?
Run a macro that calls the form’s Show method. For the example, run OpenEntryForm from the Macro dialog or assign it to a button.

Why does my UserForm say a control is not found?
The code is probably using a name that differs from the control’s (Name) property, or the event procedure is in the wrong module. Check the Properties window and the selected object in the editor.

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. Open the workbook in desktop Excel, or choose a browser-based alternative for web data collection.

Can I undo a UserForm macro with Ctrl+Z?
No. Excel’s Undo history does not reverse changes made by VBA. Save a backup before running a macro, especially if it writes to or changes existing data.

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.