Excel VBA Data Entry Form Template: Build and Use One

VBA & macros · Updated November 21, 2026

An Excel VBA data entry form template is a reusable UserForm that collects values, checks them, and appends a complete record to an Excel table. In Microsoft 365 for Windows, create a UserForm in the Visual Basic Editor, add the controls named below, and paste in the sample code; save a backup first because a macro’s changes cannot be undone with Ctrl+Z.

What the template does

The example here is designed for a simple records list. A form asks for a date, name, category, amount, and optional notes. When a user selects Save, VBA checks the required entries and adds a row to a structured Excel table named tblRecords on a worksheet named Records. The template is intentionally small enough to adapt, but it demonstrates the core pattern: collect, validate, and write.

A form is useful when people enter records repeatedly, when columns have specific expected values, or when direct editing of the worksheet creates mistakes. It does not make the workbook a database or automatically protect it from every error. The workbook still needs sensible access controls, backups, and a clear process for correcting or removing records.

Prepare the worksheet and table

Start on a worksheet named Records. In row 1, enter these exact column headings: Date, Name, Category, Amount, and Notes. Select the headings and at least one blank row, choose Insert > Table, and confirm that the table has headers. With a cell in the table selected, use Table Design > Table Name and set its name to tblRecords. Column and table names in this setup must match the code.

Using an Excel table rather than a fixed range means that new records become part of the expanding table. Table filters, formatting, and structured references can then include the added rows. Keep one record per row and one field per column; merged cells and decorative title rows inside the table make reliable data handling harder.

Create the UserForm and its controls

To show the Developer tab in Excel for Microsoft 365 on Windows, choose File > Options > Customize Ribbon, select Developer, and choose OK. Open Developer > Visual Basic. In the Visual Basic Editor, choose Insert > UserForm. Use the Toolbox to add labels and these controls, then set each control’s (Name) property in the Properties window:

  • Text boxes: txtDate, txtName, txtAmount, and txtNotes.
  • A ComboBox: cboCategory.
  • Command buttons: cmdSave, cmdClear, and cmdCancel.

Set the buttons’ visible captions to Save, Clear, and Cancel. Add labels beside the input controls so users know what to enter. The form’s object name can remain UserForm1 for this example. Control names matter because VBA refers to them directly; changing a name in the Properties window requires updating its references in the code.

Paste in the VBA template

Double-click the form’s background to open its code window and paste the following code. The category list is sample data: replace those options with categories appropriate to the workbook. This code uses named table columns, so the table can be rearranged without changing the fields’ column positions.

Option Explicit

Private Sub UserForm_Initialize()
    ' Replace these sample categories with your own.
    cboCategory.AddItem "Office"
    cboCategory.AddItem "Travel"
    cboCategory.AddItem "Supplies"
    ClearInputs
End Sub

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim newRow As ListRow

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

    If Not IsDate(txtDate.Value) Then
        MsgBox "Enter a valid date.", vbExclamation
        txtDate.SetFocus
        Exit Sub
    End If

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

    If Len(Trim$(txtAmount.Value)) = 0 Or Not IsNumeric(txtAmount.Value) Then
        MsgBox "Enter a numeric amount.", vbExclamation
        txtAmount.SetFocus
        Exit Sub
    End If

    Set ws = ThisWorkbook.Worksheets("Records")
    Set tbl = ws.ListObjects("tblRecords")
    Set newRow = tbl.ListRows.Add

    With newRow.Range
        .Cells(1, tbl.ListColumns("Date").Index).Value = CDate(txtDate.Value)
        .Cells(1, tbl.ListColumns("Name").Index).Value = Trim$(txtName.Value)
        .Cells(1, tbl.ListColumns("Category").Index).Value = cboCategory.Value
        .Cells(1, tbl.ListColumns("Amount").Index).Value = CDbl(txtAmount.Value)
        .Cells(1, tbl.ListColumns("Notes").Index).Value = Trim$(txtNotes.Value)
    End With

    MsgBox "Record added.", vbInformation
    ClearInputs
End Sub

Private Sub cmdClear_Click()
    ClearInputs
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub ClearInputs()
    txtDate.Value = Date
    txtName.Value = vbNullString
    cboCategory.ListIndex = -1
    txtAmount.Value = vbNullString
    txtNotes.Value = vbNullString
End Sub

The checks prevent saving a blank name, an unrecognized date, an unselected category, or a nonnumeric amount. They are basic input checks, not a complete data-quality system. For example, IsNumeric accepts values according to the computer’s regional settings, and date interpretation can also vary by locale. If users enter dates in a fixed format or amounts with currency symbols, specify that format clearly and adjust validation to match the workbook’s rules.

Add a macro to open the form

In the Visual Basic Editor, choose Insert > Module and add a short procedure to display the form. This example assumes that its object name is UserForm1:

Option Explicit

Public Sub OpenEntryForm()
    ' Display the data-entry form.
    UserForm1.Show
End Sub

Return to Excel and run OpenEntryForm from Developer > Macros. You can also assign the macro to a shape on a worksheet: insert a shape, right-click it, choose Assign Macro, and select OpenEntryForm. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm); saving as .xlsx does not preserve VBA macros.

Adapt the template to real work

Before distributing a form, decide what makes a record valid and encode those rules explicitly. For instance, an expense log may require an amount greater than zero, while an inventory form may need a whole-number quantity and a unique item code. The sample checks that an amount is numeric but does not reject negative numbers or duplicates, because those rules depend on the use case.

  • Required fields: Add a check for every field that must not be blank, and identify the field in the message.
  • Allowed values: Populate a ComboBox from a controlled list, or load its choices from a worksheet range so an authorized person can maintain them.
  • Duplicate prevention: Check the relevant table column for an existing ID before adding the row, if duplicate IDs would be an error.
  • Data types: Apply date and number formats to the table columns, and decide how to handle decimals, negative values, and blank optional fields.
  • Audit needs: If records need a timestamp, user identity, approval status, or edit history, add those fields deliberately. A basic form does not create a dependable audit trail by itself.

For a larger form, separate data-writing logic into its own procedure and provide clear error handling for missing sheets, renamed tables, or protected worksheets. If writing a record fails partway through, the user should receive a useful message rather than a vague VBA error. Also consider whether users should be able to edit existing records; the sample only appends a new row and does not provide search, update, or delete functions.

Common problems and safety

If the macro reports that a subscript is out of range, check that the worksheet is named exactly Records. If VBA says a table or column was not found, verify tblRecords and all five headings, including spelling and spaces. A “Method or data member not found” compile error often points to a control name that differs from the code. In the Visual Basic Editor, choose Debug > Compile VBAProject to catch many code and naming errors before running the form.

Macros are disabled or restricted in some work environments. Only enable macros in a workbook from a source you trust, and follow your organization’s security policy; do not lower Excel’s macro security setting just to run an unfamiliar file. Keep a clean backup before testing. VBA changes to worksheet cells cannot be reversed with Ctrl+Z, even if the macro’s actions appear simple. Test on a copy with sample records before using real data.

This template is for desktop Excel. Excel for the web can open an .xlsm workbook, but it cannot create, run, or edit VBA macros in the browser. On Excel for Mac, VBA is available, but the Visual Basic Editor interface, keyboard shortcuts, and some controls or integrations can differ from Windows. Use the Developer tab and Visual Basic Editor where available, and test the form in the actual Mac environment if Mac users will rely on it.

When to choose another approach

A VBA form is a reasonable choice when the workbook is used in desktop Excel, the entry process has a few clear fields, and the organization permits macros. It may be a poor fit when many people need simultaneous entry, when users primarily work in a browser or on phones, or when records require robust permissions and an auditable history. A shared workbook with VBA is not automatically a multi-user database; concurrent edits and macro execution need deliberate testing.

For a simple one-off list, direct entry into an Excel table with Data Validation dropdowns may be easier to maintain. Excel also has a built-in Data Form command for table records; it is separate from a custom VBA UserForm and has more limited layout and behavior. For browser-based collection, Microsoft Forms or another approved data-collection service may fit better, depending on licensing, storage, and organizational requirements. Choose based on where users work and how records must be managed, not only on how quickly a form can be built.

Frequently Asked Questions

Is there a free Excel VBA data entry form template?

The sample above can serve as a starting template without downloading a file. Create the table and controls with the specified names, paste the code into the form, and save as .xlsm. Review and test any downloaded macro-enabled template before enabling its macros.

Can I use a VBA data entry form in Excel for the web?

No. Excel for the web does not run VBA macros or provide the Visual Basic Editor. Use desktop Excel to run this form, or choose a browser-compatible collection method if users must enter records online.

Why does the form say it cannot find the table?

The code looks for a table named tblRecords on a worksheet named Records. Check the table name under Table Design and the worksheet tab name, then confirm that the table’s headings match the names used in the code.

Can the form edit or delete existing rows?

Not as written. This template only adds a new row. Editing or deleting safely requires a way to locate the correct record, such as a unique ID, load its values into the form, and confirm changes before updating or removing the table row.

Can I change the fields and categories?

Yes. Add or remove controls and table columns, then update the code that validates and writes each value. Change the sample AddItem lines to the categories you use, or load choices from a maintained list. Keep control names, table headings, and their VBA references consistent.

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.