VBA UserForm Data Entry: A No-Training Expense Logging Form

This guide is part of the FinDataPro Excel VBA Workbook Events series.

The Code Behind the Form

This one starts differently — build a UserForm named frmExpenseEntry in the VBA editor first (Insert → UserForm), with a date textbox (txtDate), a category combobox (cboCategory), a description textbox (txtDescription), an amount textbox (txtAmount), and Submit/Cancel buttons. Then double-click the form to open its code module and paste:

Option Explicit

Private Sub UserForm_Initialize()
    With cboCategory
        .AddItem "Travel"
        .AddItem "Meals"
        .AddItem "Office Supplies"
        .AddItem "Other"
    End With
    txtDate.Value = Format(Date, "dd/mm/yyyy")
End Sub

Private Sub cmdSubmit_Click()
    If Trim(txtDescription.Value) = "" Then
        MsgBox "Please enter a description.", vbExclamation
        Exit Sub
    End If
    If Not IsNumeric(txtAmount.Value) Then
        MsgBox "Amount must be a number.", vbExclamation
        Exit Sub
    End If

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("ExpenseLog")
    Dim nextRow As Long
    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    ws.Cells(nextRow, "A").Value = txtDate.Value
    ws.Cells(nextRow, "B").Value = cboCategory.Value
    ws.Cells(nextRow, "C").Value = txtDescription.Value
    ws.Cells(nextRow, "D").Value = CDbl(txtAmount.Value)

    MsgBox "Expense logged.", vbInformation
    txtDescription.Value = "": txtAmount.Value = ""
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

Nothing reaches the worksheet until both validation checks pass.

Where the Code Goes

This code lives behind the form itself, not a standard module — double-click the form object in the VBA editor's Project window to reach its dedicated code window. A small standard-module launcher opens it for the user:

Option Explicit

Sub OpenExpenseForm()
    frmExpenseEntry.Show
End Sub

Assign that Sub to a Form Control button placed directly on a worksheet, and the person entering expenses never needs to open the VBA editor at all.

Finance Use Case: Expense Logging for Non-Finance Staff

Handing a raw spreadsheet to staff outside finance for expense logging invites exactly the errors a UserForm prevents: a date typed into the wrong column, a category misspelled three different ways, an amount entered as text. A form with a dropdown for category, a validated numeric field for amount, and no visible grid at all removes the entire category of spreadsheet-navigation mistakes — the person filling it in never needs training beyond "fill in the boxes and click Submit."

Writing to a Protected Sheet

Pair this with a protected ExpenseLog sheet so no one can edit past entries directly in the grid. Apply ws.Protect UserInterfaceOnly:=True once, and the form's own VBA writes still succeed even though manual editing of the sheet is blocked — the two protections are independent.

Common Mistakes

• Naming controls with the Toolbox's default names (TextBox1, CommandButton1) instead of renaming them to match the code — the code will not compile against mismatched names.
• Saving the file as .xlsx at any point, which silently strips the entire form along with its code.
• Validating only some fields and letting others through blank — check every field that the log depends on, not just the obvious one.
• Forgetting Unload Me on Cancel, leaving the form technically still loaded in memory after the user thinks they've closed it.

Related Guides on This Series

Pair this with MsgBox Yes/No for a confirmation step before submission on higher-stakes forms, or On Error GoTo to catch anything the field-level validation misses.

Full reference: Microsoft Learn UserForms reference.

Frequently Asked Questions

Conclusion

A UserForm turns a spreadsheet into something a non-finance colleague can use without a single minute of training — validate every field before it writes, protect the log sheet underneath it, and give them one button to click. Pair it with MsgBox Yes/No for an extra confirmation step on anything higher-stakes than an expense log.

Prashant Panchal• ACA | FMVA® | 19 Years in Finance

Prashant Panchal is a Chartered Accountant (ACA) and Financial Modelling & Valuation Analyst (FMVA®) with 19 years of experience in finance, FP&A, and financial modelling across the GCC region. He is the founder of FinDataPro.