Build an Excel data entry form in 10 minutes.

We’ll build a form for a simple sales log with four fields: date, customer, category and amount. Once it works, you can add as many fields as you like with the same pattern.

Step 1: Turn your data into an Excel table

1. Rename a worksheet to Entries and type these headers in A1:D1: Date, Customer, Category, Amount.
2. Click a header cell and press Ctrl+T, tick My table has headers, and click OK.
3. On the Table Design tab, change the table name to tblEntries.

An Excel table grows by itself, keeps formatting and formulas consistent, and gives the code a reliable name to write to, even if you move it later.

Step 2: Draw the form

1. Press Alt+F11 to open the VBA editor, then choose Insert → UserForm.
2. In the Properties window (F4), set the form’s (Name) to frmEntry and its Caption to New entry.
3. From the Toolbox, add four labels and these controls, setting each one’s (Name):

Tip: set cboCategory’s Style to 2 - fmStyleDropDownList so people can only pick from the list, and set cmdSave’s Default to True so Enter saves.

Step 3: Add the form’s code

Double-click the form to open its code window, delete anything already there, and paste this:

Private Sub UserForm_Initialize()
    txtDate.Value = Format(Date, "yyyy-mm-dd")
    cboCategory.List = Array("Sale", "Refund", "Expense")
End Sub

Private Sub cmdSave_Click()
    ' Check every field before anything is written
    If Not IsDate(txtDate.Value) Then
        MsgBox "Please enter a valid date.", vbExclamation
        txtDate.SetFocus: Exit Sub
    End If
    If Trim(txtCustomer.Value) = "" Then
        MsgBox "Please enter a customer name.", vbExclamation
        txtCustomer.SetFocus: Exit Sub
    End If
    If cboCategory.ListIndex = -1 Then
        MsgBox "Please pick a category.", vbExclamation
        cboCategory.SetFocus: Exit Sub
    End If
    If Not IsNumeric(txtAmount.Value) Then
        MsgBox "Please enter the amount as a number.", vbExclamation
        txtAmount.SetFocus: Exit Sub
    End If

    Dim tbl As ListObject, newRow As ListRow
    Set tbl = ThisWorkbook.Worksheets("Entries").ListObjects("tblEntries")

    ' A brand-new table has one empty row: fill that instead of adding below it
    If tbl.ListRows.Count = 1 Then
        If Application.WorksheetFunction.CountA(tbl.ListRows(1).Range) = 0 Then
            Set newRow = tbl.ListRows(1)
        End If
    End If
    If newRow Is Nothing Then Set newRow = tbl.ListRows.Add

    With newRow.Range
        .Cells(1, 1).Value = CDate(txtDate.Value)
        .Cells(1, 2).Value = Trim(txtCustomer.Value)
        .Cells(1, 3).Value = cboCategory.Value
        .Cells(1, 4).Value = CDbl(txtAmount.Value)
    End With

    ' Clear the form for the next entry (keep the date)
    txtCustomer.Value = ""
    txtAmount.Value = ""
    cboCategory.ListIndex = -1
    txtCustomer.SetFocus
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Step 4: Add a button that opens the form

1. In the VBA editor choose Insert → Module and paste:

Sub ShowEntryForm()
    frmEntry.Show
End Sub

2. Back in Excel, choose Insert → Shapes, draw a rounded rectangle on the sheet and type New entry on it.
3. Right-click the shape, choose Assign Macro, pick ShowEntryForm and click OK.
4. Save the workbook as Excel Macro-Enabled Workbook (*.xlsm). A normal .xlsx file throws the code away.

Click the button, fill in the form and press Enter. The entry appears as a new row in tblEntries, and the form clears itself for the next one.

Make it your own

• More fields: add a control, add a column to the table, and add one line inside the With block, such as .Cells(1, 5).Value = txtNotes.Value.
• Categories from a list on a sheet: replace the Array(...) line with cboCategory.List = Worksheets("Lists").Range("A2:A20").Value.
• Block duplicates: before saving, check the new value with Application.WorksheetFunction.CountIf on the table column and stop if it’s already there.
• Mac users: Excel for Mac can run userforms, but its form designer is more limited. Build the form on Windows if you can.

If something goes wrong

• “Subscript out of range”: the sheet isn’t named exactly Entries, or the table isn’t named tblEntries.
• The button does nothing: macros are blocked. Close the file, right-click it in File Explorer, open Properties, tick Unblock, and reopen it with macros enabled.
• Dates show as numbers: format the Date column of the table as a date.