📎 This article includes 2 downloadable practice files ↓

Shared sheets get messy because people type straight into them: dates in three formats, “Delhi ” with a trailing space, an amount typed as text, a row added below the totals. A UserForm puts a proper window in front of the sheet. People pick from lists, the form checks what they entered, and only clean data reaches your table.

We’ll build a small expense-entry form: date, category (from a list), amount, paid by, note.

1. Create the form

1. In the VBA editor: Insert › UserForm.
2. Press F4 for the Properties window and set (Name) to frmExpense and Caption to “Add expense”.
3. From the Toolbox (View › Toolbox if it’s hidden), drag on: 5 Labels, 3 TextBoxes, 2 ComboBoxes and 2 CommandButtons.

Name the controls — this matters more than anything else on a form, because the code refers to them by name:

2. Fill the lists when the form opens

Double-click the form background to open its code, then choose UserForm › Initialize from the drop-downs:

Private Sub UserForm_Initialize()
    txtDate.Value = Format(Date, "dd-mm-yyyy")
    cboCategory.List = ThisWorkbook.Worksheets("Lists").Range("A2:A12").Value
    cboPaidBy.List = Array("Cash", "Card", "UPI", "Bank transfer")
    cboCategory.Style = fmStyleDropDownList      ' pick only, no typing
    cboPaidBy.Style = fmStyleDropDownList
End Sub

Reading the categories from a Lists sheet means anyone can add a category without touching the code.

3. Validate, then save

Private Sub cmdSave_Click()
    ' --- check every input before writing anything ---
    If Not IsDate(txtDate.Value) Then
        MsgBox "Please enter a valid date (dd-mm-yyyy).", vbExclamation: txtDate.SetFocus: Exit Sub
    End If
    If cboCategory.ListIndex = -1 Then
        MsgBox "Pick a category.", vbExclamation: cboCategory.SetFocus: Exit Sub
    End If
    If Not IsNumeric(txtAmount.Value) Or Val(txtAmount.Value) <= 0 Then
        MsgBox "Amount must be a number above zero.", vbExclamation: txtAmount.SetFocus: Exit Sub
    End If
    If cboPaidBy.ListIndex = -1 Then
        MsgBox "How was it paid?", vbExclamation: cboPaidBy.SetFocus: Exit Sub
    End If

    ' --- add a row to the table ---
    Dim tbl As ListObject, r As ListRow
    Set tbl = ThisWorkbook.Worksheets("Expenses").ListObjects("tblExpenses")
    Set r = tbl.ListRows.Add
    r.Range.Value = Array(CDate(txtDate.Value), cboCategory.Value, CDbl(txtAmount.Value), _
                          cboPaidBy.Value, Trim$(txtNote.Value), Environ$("USERNAME"))

    ' --- ready for the next entry ---
    txtAmount.Value = "": txtNote.Value = "": cboCategory.ListIndex = -1
    cboCategory.SetFocus
End Sub

Private Sub cmdClose_Click()
    Unload Me
End Sub

Writing into an Excel Table (Ctrl+T) instead of “the next empty row” is the key decision: the table grows by itself, formulas and formatting copy down, and pivot tables based on it pick up the new rows.

4. Show the form from a button

In a normal module:

Sub ShowExpenseForm()
    frmExpense.Show
End Sub

Then Insert › Shapes on the sheet, right-click the shape, Assign Macro, pick ShowExpenseForm.

Modal or modeless?

frmExpense.Show is modal: users can’t touch the sheet until the form closes. frmExpense.Show vbModeless lets them scroll the sheet while the form stays open — nice for look-ups, but then your code must cope with the sheet changing underneath it.

Where people go wrong

Practice

The workbook has the finished form, a Lists sheet, the tblExpenses table with a monthly summary pivot, and a second, unfinished form for you to complete (a customer form with mobile-number validation). For a bigger example see the full data-entry project.

Next: Collections and Dictionaries — the fastest way to find unique values, count and group in VBA.