Excel VBA UserForm Tutorial: Build a Data Entry Form

To build an Excel VBA UserForm, open the Visual Basic Editor, insert a UserForm, add controls such as text boxes and buttons, and write event code that validates and saves entries to a worksheet. This tutorial creates a simple contact form for Microsoft Excel for Microsoft 365 on Windows, including a department drop-down and Save and Cancel buttons.

What a VBA UserForm Does

A UserForm is a custom window for collecting information or presenting choices while a VBA macro runs. Instead of asking someone to type directly into worksheet cells, a form can label each field, guide the order of entry, check required values, and put the finished record in a consistent place. It is useful for small intake tools, checklists, and internal data-entry workflows.

A UserForm is not a worksheet feature or a separate database. Its controls and code are stored in the VBA project inside the workbook, and its actions usually read or write workbook data. It also does not automatically make an Excel file a secure application: users who can edit the workbook may be able to inspect or change its VBA project, subject to any project protection you apply.

Prepare the Workbook and Open the VBA Editor

First, save a working copy of the workbook as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx workbook cannot store VBA macros. Before running any macro, keep a separate backup: VBA changes cannot be reversed with Ctrl+Z.

Create a worksheet named Records. Enter Name, Email, and Department in cells A1, B1, and C1.
If the Developer tab is not visible, choose File > Options > Customize Ribbon, select Developer in the Main Tabs list, and choose OK.
Open the Visual Basic Editor with Alt+F11. On a Mac, Option+F11 is commonly used; some keyboards require Fn as well. The Mac editor and keyboard shortcuts can differ.
In the editor, select your workbook project, then choose Insert > UserForm. If you do not see the Properties window, press F4.

Excel for the web can open an .xlsm file, but it cannot create, edit, or run VBA macros. Use the desktop Excel application for this tutorial. Excel for Mac has a VBA editor, but UserForm support and available controls can differ from Windows; avoid assuming a Windows-only control or design will behave identically on a Mac.

Design the Form and Name Its Controls

Select the blank form and use the Properties window to set its (Name) to frmEntry and its Caption to New Contact. The name is how VBA refers to the form; the caption is what appears in its title bar. Open the Toolbox if it is not visible, then add controls by selecting a control and drawing it on the form.

Add three Labels, with captions Name, Email, and Department.
Add two TextBoxes and set their (Name) properties to txtName and txtEmail.
Add a ComboBox named cboDepartment for the department choices.
Add two CommandButtons named cmdSave and cmdCancel. Set their captions to Save and Cancel.

Control names matter because the code refers to them exactly. Use the Properties window rather than changing only the visible captions. Arrange labels beside their fields, keep the buttons easy to distinguish, and make the form large enough that users do not have to guess which field they are filling in. You can also set a control’s TabIndex so pressing Tab moves through the fields in a sensible order.

Add the Form Event Code

Double-click the form background to open its code window. Add the following procedures. The Initialize event fills the department list when the form opens. The Save button checks that the required fields are present, then writes the values to the next available row on the Records worksheet.

Private Sub UserForm_Initialize()
    ' Load the choices shown in the department drop-down.
    Me.cboDepartment.Clear
    Me.cboDepartment.AddItem "Sales"
    Me.cboDepartment.AddItem "Support"
    Me.cboDepartment.AddItem "Operations"
    Me.cboDepartment.AddItem "Other"
End Sub

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 the values entered in the form.
    personName = Trim$(CStr(Me.txtName.Value))
    emailAddress = Trim$(CStr(Me.txtEmail.Value))
    department = Trim$(CStr(Me.cboDepartment.Value))

    ' Do not write a record when a required field is empty.
    If Len(personName) = 0 Then
        MsgBox "Enter a name before saving.", vbExclamation
        Me.txtName.SetFocus
        Exit Sub
    End If

    If Len(emailAddress) = 0 Or InStr(1, emailAddress, "@", vbTextCompare) = 0 Then
        MsgBox "Enter an email address containing @.", vbExclamation
        Me.txtEmail.SetFocus
        Exit Sub
    End If

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

    ' Append the record below the existing names in column A.
    Set ws = ThisWorkbook.Worksheets("Records")
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
    ws.Cells(nextRow, 1).Value = personName
    ws.Cells(nextRow, 2).Value = emailAddress
    ws.Cells(nextRow, 3).Value = department

    MsgBox "The record was saved.", vbInformation
    Me.txtName.Value = vbNullString
    Me.txtEmail.Value = vbNullString
    Me.cboDepartment.ListIndex = -1
    Me.txtName.SetFocus
End Sub

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

The email check is intentionally basic: it only looks for an at sign and is not proof that an address exists or is correctly formatted. Real applications may need stronger validation, duplicate checks, or additional fields. The code expects the Records sheet and its headers to exist; if the sheet is renamed or deleted, the save procedure will stop with a VBA error.

Create a Macro That Opens the Form

The form needs an entry point so someone can display it from Excel. In the Visual Basic Editor, choose Insert > Module, then add this procedure to the standard module:

Public Sub ShowEntryForm()
    ' Display the contact-entry form.
    frmEntry.Show
End Sub

Return to Excel and run ShowEntryForm from Developer > Macros, or assign that macro to a button. To add a worksheet button, choose Developer > Insert and select Button under Form Controls, draw it on the sheet, and select ShowEntryForm when Excel asks which macro to assign. Form Controls are different from ActiveX controls; the button here is just a convenient way to launch the VBA procedure.

Test the Entry and Understand Where It Goes

Before sharing the workbook, test both successful and unsuccessful entries. Open the form, leave Name blank, and select Save: the form should show a message and focus the missing field. Repeat with an email value that has no at sign and with no department selected. Then enter all three values and save. The new row should appear below the headers on Records, and the form should clear its fields ready for another entry.

The code uses ThisWorkbook, which means the workbook containing the VBA project. This is usually preferable to ActiveWorkbook, which refers to whichever workbook currently has focus and could be a different file. The next-row calculation looks down column A, so the sheet should keep a name in column A for every saved record. If the workbook already has unrelated data in that column, choose a dedicated data sheet or adjust the storage design.

Common Problems and Safe Macro Use

“Subscript out of range” on save: Check that the worksheet tab is named exactly Records. A trailing space or a different spelling counts as a different name.
“Method or data member not found” while editing: Compare every code reference with the control’s (Name) property. For example, code using txtEmail will not find a control named TextBox2.
The macro does not appear in the macro list: Confirm the launcher is in a standard module and is a public procedure without parameters. Save the file as .xlsm.
Excel blocks the macro: Only enable macros in workbooks you trust. Follow your organization’s security policy and use Excel’s trusted locations or file settings where appropriate; do not broadly enable all macros just to make one workbook run.

VBA code can change or delete workbook content, and the interface does not make a macro safe by itself. Test with a copy and sample records first. If a form is distributed to others, explain what it stores, where the data is written, and who can access the workbook. Password-protecting a VBA project can deter casual viewing, but it should not be treated as strong data security.

When a UserForm Is the Right Choice

A UserForm is useful when users need a guided entry window, several related fields, or validation that is awkward to express with ordinary cells. A worksheet-based form may be simpler when users need to see and edit many records at once, sort or filter the data, or collaborate in a workbook. Excel tables, data validation, and worksheet protection can provide a lower-maintenance option for straightforward entry.

For a team workflow that needs simultaneous submissions, centralized access, or browser-based use, a macro-enabled workbook may not be the best fit. Microsoft Forms, a SharePoint list, or a Power Apps solution may be worth considering, depending on the Microsoft 365 services available and the organization’s requirements. VBA UserForms are desktop-oriented and rely on users opening the workbook in an environment that supports VBA.

Frequently Asked Questions

How do I open a UserForm in Excel?
Run a public macro that calls the form’s Show method, such as frmEntry.Show. You can run it from Developer > Macros or assign it to a Form Control button on a worksheet.

Can I create a UserForm without VBA?
No. A VBA UserForm is part of an Excel VBA project and requires VBA code to create, display, or respond to its controls. For no-code data entry, consider worksheet cells with data validation or an appropriate Microsoft Forms workflow.

Can Excel for the web run a VBA UserForm?
No. Excel for the web does not run VBA macros or display VBA UserForms. Open the macro-enabled workbook in desktop Excel, and check platform support if users work on Mac.

Why does my form say it cannot find the worksheet?
The code refers to a worksheet named exactly Records in the workbook that contains the VBA. Create that sheet or update the name in the code to match the actual worksheet tab.

Can I undo a UserForm save with Ctrl+Z?
No. Excel’s Undo history does not reverse changes made by a VBA macro. Keep a backup before running code and test the form on a copy of the workbook.

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.