Excel VBA UserForm Data Entry: Build a Simple Form

VBA & macros · Updated November 2, 2026

To enter data through an Excel VBA UserForm, create a form with text boxes and a Save button, validate the entries in the button’s VBA event, then write the values to the next available row on a worksheet. In Excel for Microsoft 365 on Windows, the example below saves a name, an ISO-formatted date, an amount, and notes to a sheet named Data.

What a UserForm does

An Excel UserForm is a custom dialog built with VBA controls such as text boxes, labels, and command buttons. Rather than typing directly into worksheet cells, a user fills in named fields and clicks Save. VBA can check that required values are present and in the expected format before putting them into a table-like range.

This approach is useful when people repeatedly add records, when the worksheet layout should remain out of sight, or when consistent input matters. A form can make a workbook friendlier, but it is not a database or a substitute for access controls. The saved records still reside in the workbook, and anyone with sufficient access to that file may be able to change them.

Prepare the workbook and worksheet

First, make a copy of the workbook. The macro below adds rows, but VBA changes cannot be reversed with Ctrl+Z. Create a worksheet named Data and put these headers in row 1:

  • Column A: Name
  • Column B: Date
  • Column C: Amount
  • Column D: Notes

Keep each record on one row. The sample determines the next row by looking for the last occupied cell in column A, so it expects every saved record to have a name and does not handle deliberately blank name cells. If your workbook already contains a table with a different structure, adjust the column references in the code rather than pasting it unchanged.

Save the file as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. A standard .xlsx workbook cannot store VBA project code. On Windows, show the Developer tab if it is hidden: choose File > Options > Customize Ribbon, then select Developer.

Create the form and its controls

On the Developer tab, choose Visual Basic, or press Alt+F11. In the Visual Basic Editor, choose Insert > UserForm. If the Toolbox is not visible, use View > Toolbox. Add four text boxes, four labels, and two command buttons. Arrange the controls so users can identify each field, then set the controls’ (Name) properties in the Properties window as follows:

  • Text boxes: txtName, txtDate, txtAmount, and txtNotes.
  • Save button: cmdSave, with its Caption set to Save.
  • Cancel button: cmdCancel, with its Caption set to Cancel.

Labels are for display, so their names do not affect this example. The names of the text boxes and buttons do matter: VBA refers to those exact names. In the form’s Properties window, you can set its Caption to something like Add a Record.

Add the Save and Cancel code

Double-click the Save button to open its code window and put the following event procedure in the UserForm’s code module. Add the Cancel procedure there as well. The date field deliberately requires yyyy-mm-dd, such as 2026-11-02, so the year, month, and day can be checked without relying on a user’s regional date order. Amounts use the Windows regional decimal convention.

Option Explicit

Private Sub cmdSave_Click()
    Dim ws As Worksheet
    Dim dateParts() As String
    Dim entryDate As Date
    Dim yearPart As Long
    Dim monthPart As Long
    Dim dayPart As Long
    Dim amountValue As Double
    Dim lastRow As Long
    Dim nextRow As Long

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

    dateParts = Split(Trim$(Me.txtDate.Value), "-")
    If UBound(dateParts) <> 2 Then
        MsgBox "Enter the date as yyyy-mm-dd.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If
    If Len(dateParts(0)) <> 4 Or Len(dateParts(1)) <> 2 Or _
       Len(dateParts(2)) <> 2 Then
        MsgBox "Enter the date as yyyy-mm-dd.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(dateParts(0)) Or Not IsNumeric(dateParts(1)) Or _
       Not IsNumeric(dateParts(2)) Then
        MsgBox "The date must contain numbers in yyyy-mm-dd format.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If

    yearPart = CLng(dateParts(0))
    monthPart = CLng(dateParts(1))
    dayPart = CLng(dateParts(2))
    If yearPart < 1900 Or yearPart > 9999 Or _
       monthPart < 1 Or monthPart > 12 Or _
       dayPart < 1 Or dayPart > 31 Then
        MsgBox "Enter a valid date as yyyy-mm-dd.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If

    entryDate = DateSerial(yearPart, monthPart, dayPart)
    If Year(entryDate) <> yearPart Or Month(entryDate) <> monthPart Or _
       Day(entryDate) <> dayPart Then
        MsgBox "That date does not exist. Check the month and day.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.txtAmount.Value) Then
        MsgBox "Enter a numeric amount.", vbExclamation
        Me.txtAmount.SetFocus
        Exit Sub
    End If
    amountValue = CDbl(Me.txtAmount.Value)

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

    ' Prefix text with an apostrophe so it is stored as text, not a formula.
    ws.Cells(nextRow, 1).Value = "'" & Trim$(Me.txtName.Value)
    ws.Cells(nextRow, 2).Value = entryDate
    ws.Cells(nextRow, 2).NumberFormat = "yyyy-mm-dd"
    ws.Cells(nextRow, 3).Value = amountValue
    ws.Cells(nextRow, 4).Value = "'" & Me.txtNotes.Value

    MsgBox "Record saved to row " & nextRow & ".", vbInformation
    Unload Me
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

The apostrophe prefix on the name and notes helps keep text that begins with characters such as an equals sign from being interpreted as a worksheet formula. Excel treats the prefix as a text-entry marker, so it generally does not display as part of the cell’s visible text. The procedure converts the date into an actual Excel date value and applies a display format; the amount is stored as a number, not as text.

Show the form and test a record

The form needs a way to open. In the Visual Basic Editor, choose Insert > Module and add this separate procedure. It assumes the UserForm is named frmEntry; set that value in the form’s (Name) property if necessary.

Option Explicit

Public Sub ShowEntryForm()
    frmEntry.Show
End Sub

Return to Excel and run ShowEntryForm from Developer > Macros, or assign it to a worksheet button. To create a button, use Developer > Insert > Button (Form Control), draw the button, and assign the macro. On the first test, enter a name, a valid date such as 2026-11-02, and a number such as 125.50 if that decimal separator is accepted by your regional settings. Click Save and confirm the new record appears beneath the headers.

Try an empty name, an invalid date such as 2026-02-30, and a nonnumeric amount. Each should produce a message and prevent the row from being written. If the workbook is in a different folder or the form does not open, verify that you are running the macro in the intended workbook and that the form’s name and worksheet name match the code.

How the entry process works

When a user clicks Save, Excel runs the button’s cmdSave_Click event. The procedure checks fields before changing the sheet. It splits the date at hyphens, verifies the component lengths and numeric values, and then uses DateSerial. The follow-up comparison matters because date constructors can roll an out-of-range day into the next month; comparing the resulting parts catches an impossible date such as February 30.

After validation, the macro finds the last occupied cell in column A and writes the record to the next row. For example, if row 1 contains headers and rows 2 through 8 contain names, the next record is written in row 9. A valid date is stored as a date value, and a valid amount as a number, which allows later sorting, filtering, and arithmetic. The form then closes.

Validation, formatting, and practical improvements

Validation should reflect the rules of the data rather than simply block unusual input. This sample requires a name, date, and numeric amount, but permits negative amounts because they may represent credits or adjustments. If negatives are never valid in your process, add a separate rule after converting the amount, such as checking whether amountValue is less than zero. Do not impose that restriction without confirming what the values mean.

For a real workbook, consider these refinements:

  • Use a worksheet table, such as one created with Insert > Table, so filters and formatting can expand as records are added. If you change to a table-based approach, update the VBA to add a table row rather than assuming a fixed range.
  • Set clear formats for amounts, such as currency, based on the actual currency and reporting needs. A number format changes display, not the underlying numeric value.
  • Decide whether duplicate entries are allowed. The sample does not search for duplicates, so submitting the same details twice adds two rows.
  • Consider a confirmation message before saving if an accidental click would create a costly duplicate. For larger workflows, add a unique record ID and a deliberate duplicate-check rule.
  • Keep labels, column headers, and instructions consistent. A form field called Amount should not silently mean different things to different users.

The sample writes directly to the first four columns of the Data sheet and does not include error handling for a renamed or missing sheet, protected cells, or a full disk. Those are workbook-specific conditions. Test with a copy and add checks where your workflow requires them.

VBA forms compared with worksheet entry

A UserForm is not always the best solution. A formatted worksheet with data validation can be easier to maintain and keeps the records visible. Excel’s Data Validation command can restrict entries, for example, to a date range or a list of allowed values, without requiring a VBA form. A table also provides built-in filtering and makes it straightforward for several experienced users to enter rows.

A UserForm is more useful when the input sequence needs guidance, when people should not navigate the data sheet, or when VBA must apply several checks before saving. However, forms require macro-enabled files and users must be able to run macros under their organization’s security policy. Excel for the web does not run VBA macros. Excel for Mac has VBA capabilities, but the example is written for Windows; verify UserForm and control compatibility in the Mac version and environment you need to support before distributing the workbook.

Security and maintenance

VBA macros can change workbook contents, so only enable macros in files from sources you trust. In a workplace, follow your organization’s approved macro policy rather than weakening security settings to make a downloaded workbook run. A UserForm does not encrypt the worksheet or prevent a knowledgeable user from accessing the data.

Document the sheet name, column order, and control names alongside the workbook. If someone renames Data, deletes a column, or changes a control name, the code may stop working or write values to the wrong place. Keep a clean backup before changing code, and test changes with representative inputs, including blanks, invalid dates, decimal amounts, and text that begins with an equals sign.

Frequently Asked Questions

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

Can a UserForm add data to the next empty row?
Yes. VBA can find the last used cell in a chosen column and write to the following row. The example uses column A, so it relies on every saved record having a name in that column.

Can I use this VBA form in Excel for the web?
No. Excel for the web can open and edit many macro-enabled workbooks, but it does not run VBA macros. Use desktop Excel to run the form, or consider a non-VBA entry method for browser-based users.

Why does the date field require yyyy-mm-dd?
A fixed year-month-day format makes the example’s validation unambiguous across common regional date orders. The macro checks that the date exists and saves it as an Excel date value; it does not store the date as plain text.

Can I undo a UserForm save with Ctrl+Z?
Do not rely on it. VBA macros generally clear Excel’s undo history, so Ctrl+Z cannot reliably reverse a record written by a macro. Save a backup copy before testing or changing the code.

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.