Build a Data Entry Form in Excel VBA (UserForm Step by Step)

📎 This article includes 2 downloadable practice files ↓

When several people type into the same sheet, rows get skipped, dates get typed as text and names get misspelt. A UserForm gives them a clean form with drop-downs and checks, and writes each entry into a Table in the right format.

1. The table

On a sheet called Entries, create a Table (Ctrl+T) named tblEntries with columns Date, Customer, Region, Amount, EnteredBy. Keep drop-down lists (Regions, Customers) on a Lists sheet.

2. Design the form

Alt+F11 › Insert › UserForm. From the Toolbox add labels and: txtDate (TextBox), cboCustomer and cboRegion (ComboBox), txtAmount (TextBox), cmdSave and cmdClose (CommandButton). Set each control’s Name in the Properties window.

3. Fill drop-downs when the form opens

Private Sub UserForm_Initialize()
    cboRegion.List = Application.Transpose(Sheets("Lists").Range("A2:A5").Value)
    cboCustomer.List = Application.Transpose(Sheets("Lists").Range("C2:C50").Value)
    txtDate.Value = Format(Date, "dd-mm-yyyy")
End Sub

4. Validate and save

Private Sub cmdSave_Click()
    If Not IsDate(txtDate.Value) Then MsgBox "Enter a valid date": txtDate.SetFocus: Exit Sub
    If cboCustomer.ListIndex = -1 Then MsgBox "Pick a customer from the list": Exit Sub
    If Not IsNumeric(txtAmount.Value) Or Val(txtAmount.Value) <= 0 Then MsgBox "Amount must be a positive number": Exit Sub
    Dim r As ListRow
    Set r = Sheets("Entries").ListObjects("tblEntries").ListRows.Add
    r.Range.Value = Array(CDate(txtDate.Value), cboCustomer.Value, cboRegion.Value, CDbl(txtAmount.Value), Environ("Username"))
    txtAmount.Value = "": txtAmount.SetFocus      ' ready for the next entry
End Sub
Private Sub cmdClose_Click(): Unload Me: End Sub

5. Open it from a button

In a normal module: Sub ShowForm(): frmEntry.Show: End Sub, then Insert › Shapes on the sheet, right-click › Assign Macro. Save as .xlsm (or .xlsb).

Where people go wrong

Full course: Excel VBA course, lesson 15 on UserForms.