VBA Code for UserForm Data Entry: Build a Reliable Form

VBA & macros · Updated November 18, 2026

To enter records through an Excel VBA UserForm, create text boxes and a combo box on the form, validate the fields in its Save button, then write the values to the next row of a worksheet. The example below saves a person’s name, email, department, and start date to a sheet called Records in Excel for Microsoft 365 on Windows.

What a UserForm does

A UserForm is a custom window in an Excel workbook. Instead of typing directly into cells, a person fills in labeled fields and chooses a command such as Save or Clear. VBA code responds to those actions. A form can make a repeated entry task easier to follow, especially when a worksheet has many columns or users should not edit its layout.

A form does not automatically create a database or protect information from every kind of error. The workbook still stores the records in worksheet cells, and the code must decide which fields are required, where entries go, and what to do when a value is invalid. The sample here is intentionally small: it provides a practical starting point without relying on external libraries, Windows APIs, or add-ins.

Prepare the workbook and record layout

In a new worksheet, rename the tab Records and enter these headers in row 1: Full Name, Email, Department, and Start Date, in columns A through D. Keep column A populated for each saved record because the example uses that column to find the next available row. The first entry will go in row 2. Add any additional fields to the worksheet and form deliberately; changing the column order also means changing the corresponding VBA assignments.

Save the file as an Excel Macro-Enabled Workbook (.xlsm). A standard .xlsx file cannot retain VBA code when saved. Before running or changing a macro, save a separate copy of the workbook: macro actions cannot be reversed with Ctrl+Z. This example only appends data and clears form fields, but a backup is still sensible when working with important records.

On Windows, if the Developer tab is not visible, open File > Options > Customize Ribbon and select Developer. Open the Visual Basic Editor with Alt+F11. On a Mac, the Developer tab can be enabled in Excel > Settings (or Preferences) > Ribbon & Toolbar, depending on the release; Option+F11 opens the editor on many keyboards, sometimes with Fn as well. Excel for the web does not create or run VBA macros, so use the desktop application.

Build the form and name its controls

In the Visual Basic Editor, choose Insert > UserForm. Use the Toolbox to place four Label controls, two TextBox controls for the name and email, one ComboBox for department, one TextBox for the date, and three CommandButton controls. Set each control’s Name property in the Properties window, usually opened with F4. The Name property is what the code uses; the Caption property is the text a user sees.

  • Text boxes: txtFullName, txtEmail, and txtStartDate.
  • Combo box: cboDepartment.
  • Buttons: cmdSave, cmdClear, and cmdCancel.
  • Useful captions: Save, Clear, and Cancel. Give the labels clear captions such as Full Name and Start Date.

Names must match the code exactly, including spelling. A control’s caption can be changed without changing its name. Double-click the form to open its code window, or right-click it in Project Explorer and select View Code. Double-click a button to create that button’s Click event procedure. The complete example below belongs in the UserForm’s code module, not in a worksheet module or a standard module.

VBA code for saving and clearing entries

This code assumes the form and worksheet use the names and layout above. It fills the department list when the form opens, checks required information, and writes valid entries to columns A:D. It uses standard VBA and Excel objects available in desktop Excel.

Option Explicit

Private Sub UserForm_Initialize()
    ' Load the choices each time the form opens.
    With cboDepartment
        .Clear
        .AddItem "Sales"
        .AddItem "Support"
        .AddItem "Operations"
        .AddItem "Other"
    End With
End Sub

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim nextRow As Long
    Dim fullName As String
    Dim email As String
    Dim enteredDate As Date

    fullName = Trim$(CStr(txtFullName.Value))
    email = Trim$(CStr(txtEmail.Value))

    If fullName = vbNullString Then
        MsgBox "Enter a name before saving.", vbExclamation
        txtFullName.SetFocus
        Exit Sub
    End If

    If email = vbNullString Or InStr(1, email, "@", vbTextCompare) = 0 Then
        MsgBox "Enter an email address that includes @.", vbExclamation
        txtEmail.SetFocus
        Exit Sub
    End If

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

    If Not IsDate(txtStartDate.Value) Then
        MsgBox "Enter a valid start date.", vbExclamation
        txtStartDate.SetFocus
        Exit Sub
    End If
    enteredDate = CDate(txtStartDate.Value)

    Set ws = ThisWorkbook.Worksheets("Records")
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = fullName
    ws.Cells(nextRow, 2).Value = email
    ws.Cells(nextRow, 3).Value = cboDepartment.Value
    ws.Cells(nextRow, 4).Value = enteredDate
    ws.Cells(nextRow, 4).NumberFormat = "yyyy-mm-dd"

    MsgBox "Record saved.", vbInformation
    ClearEntryFields
End Sub

Private Sub cmdClear_Click()
    ClearEntryFields
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub ClearEntryFields()
    txtFullName.Value = vbNullString
    txtEmail.Value = vbNullString
    cboDepartment.ListIndex = -1
    txtStartDate.Value = vbNullString
    txtFullName.SetFocus
End Sub

How the save routine works

The procedure first copies the text-box values into declared String variables. Trim$ removes spaces at the start and end, so a field containing only spaces is treated as empty. The checks stop the procedure with Exit Sub when a required value is missing or the department has not been selected. That prevents the worksheet write from happening until the basic checks pass.

The email check is deliberately modest: it looks for an at sign, not whether the address exists or meets every email standard. A stronger business process might confirm an address separately or use a carefully designed validation rule. Likewise, IsDate asks whether VBA can interpret the supplied text as a date under the computer’s regional settings. The cell’s yyyy-mm-dd number format controls how the saved date is displayed; it does not control how the typed text is interpreted.

End(xlUp) starts at the bottom of column A and moves upward to its last non-empty cell. Adding one gives the row for the next record. This is appropriate for a simple append-only list with a header in A1 and a name in column A. If users delete names, leave gaps, or store records in a different structure, consider using an Excel Table or a more specific method for finding the next row.

The workbook reference uses ThisWorkbook, meaning the workbook containing the VBA code. That is safer than ActiveWorkbook for this task: ActiveWorkbook can change if the user activates another file. The worksheet name must be exactly Records or the assignment will fail with a subscript error.

Show the form and test it carefully

To display the form during development, place this short procedure in a standard module. In the Visual Basic Editor, choose Insert > Module, then add:

Option Explicit

Public Sub ShowEntryForm()
    UserForm1.Show
End Sub

Replace UserForm1 with the actual form name shown in its Properties window if you renamed it. Run ShowEntryForm from the editor with the cursor inside the procedure and press F5, or return to Excel and run it through Developer > Macros. You can also assign a macro to a worksheet button, but that is optional. A worksheet button is not the same as one of the command buttons placed inside the UserForm.

Test more than a successful entry. Try leaving the name blank, entering an email without an at sign, skipping the department, and typing an invalid date. Confirm that each invalid attempt leaves the worksheet unchanged. Then save a normal record and check that it appears on the next row, that the date is a real date value, and that the form clears afterward. If the form will be used by others, test with a copy of the workbook and representative data before distributing it.

Validation and data-quality choices

Validation should reflect the task rather than merely make the form look strict. Decide which fields can be blank, which values must come from an approved list, and how dates should be entered. A ComboBox populated with fixed choices reduces spelling variations, but someone can still alter the choices in code. If departments change frequently, store the choices on a worksheet and load them from there instead of editing AddItem lines.

For a date field, a text box is easy to build but can be confusing across regional settings. Tell users which date format their Excel environment recognizes, or use a date-selection approach that has been checked for the versions and platforms you support. Do not assume that displaying yyyy-mm-dd after saving makes every input format unambiguous. If records need unique IDs, duplicate detection, or audit information, add those requirements explicitly; this sample does not provide them.

Common errors and practical fixes

  • Subscript out of range: Check that the worksheet tab is named Records and that the code uses ThisWorkbook.Worksheets("Records").
  • Variable or method not found: Compare every control’s Name property with the names in the code. For example, a control named TextBox1 will not respond to code referring to txtFullName.
  • Code runs but writes to the wrong place: Verify the headers and the column assignments. The sample writes name, email, department, and date to A, B, C, and D.
  • Compile error on a button procedure: Confirm that the event procedure is in the UserForm module and that the button’s name matches the event prefix, such as cmdSave_Click.
  • Dates appear as numbers: Excel stores dates as serial values. Apply a suitable number format to column D; the code also formats each saved date.
  • Macros do not run: Confirm the file is .xlsm and opened in desktop Excel, and check the workbook’s security notification. Do not enable macros in files from untrusted sources.

Using Option Explicit at the top makes VBA require declared variables, helping catch spelling mistakes in variable names. It does not catch a misspelled worksheet tab or guarantee that every user input is suitable for the task, so test the form and its output separately.

Compatibility and safe use

The example uses ordinary UserForm controls and worksheet operations rather than Windows-only API calls, but it is written for Excel desktop on Windows. Excel for the web cannot run VBA. Excel for Mac supports VBA, but form controls, editor access, and some details can differ by Excel release; check the finished form on the actual Mac versions used by your audience. The shortcut to open the editor can also depend on the keyboard’s function-key settings.

Macros can change workbook contents, and Ctrl+Z does not undo a macro’s actions. Keep a backup before running new code, limit access to sensitive workbooks, and distribute macro-enabled files only through channels you trust. A form is an entry interface, not a security boundary: users who can access the workbook may be able to inspect or change its data or code.

When a different entry method makes sense

A UserForm is useful when a workbook-based workflow needs a guided interface and a small amount of custom validation. For a straightforward list, typing directly into an Excel Table may be faster and simpler. Data Validation lists can constrain worksheet entries without VBA, while a protected worksheet can help prevent accidental layout changes; neither is a substitute for robust access controls.

If multiple people need to submit records at the same time, or the information is business-critical and must be centrally managed, a shared workbook with VBA may not be the right storage design. Consider an approved database, a Microsoft 365 form or application, or another organization-supported data-entry system. The right choice depends on collaboration, permissions, auditing, maintenance, and the systems available to the organization.

Frequently Asked Questions

Where do I paste VBA code for a UserForm?
Paste the form’s event procedures into the UserForm code module. Put a procedure that opens the form, such as ShowEntryForm, in a standard module. Code in a worksheet module is not the right home for the form’s button events.

How do I add a drop-down list to an Excel UserForm?
Add a ComboBox from the Visual Basic Editor’s Toolbox, give it a name such as cboDepartment, then populate it in UserForm_Initialize with AddItem statements or values from a worksheet. The sample uses AddItem for a short, fixed list.

Why does my UserForm say Subscript out of range?
Most often, the worksheet name in the code does not exactly match a sheet tab, or the code is referring to a workbook or collection item that is not present. Check the spelling of Records and confirm the sheet is in the workbook containing the code.

Can I use an Excel VBA UserForm in a browser?
No. Excel for the web does not run VBA macros or display VBA UserForms. Open the macro-enabled workbook in desktop Excel to use this approach.

Can the Save button prevent duplicate records?
Not in the example as written. Duplicate checks require a defined rule, such as whether email addresses must be unique, and code that searches existing records before writing. Decide how to handle case differences and blank values before adding that logic.

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.