A UserForm gives people a simple window to enter records, so nobody types into the wrong column of your data sheet.

1. Create the form

In the VBA editor (Alt+F11): Insert → UserForm. From the Toolbox add:

• TextBox txtName, TextBox txtAmount, TextBox txtDate
• ComboBox cboRegion
• CommandButtons btnSave and btnClose
• Labels next to each field

Name the form frmEntry (Properties window → Name).

2. Fill the dropdown when the form opens

Private Sub UserForm_Initialize()
    cboRegion.List = Array("North", "South", "East", "West")
    txtDate.Value = Format(Date, "dd-mmm-yyyy")
End Sub

3. Validate and save

Private Sub btnSave_Click()
    Dim ws As Worksheet, r As Long
    If Trim(txtName.Value) = "" Then MsgBox "Enter a name": txtName.SetFocus: Exit Sub
    If Not IsNumeric(txtAmount.Value) Then MsgBox "Amount must be a number": txtAmount.SetFocus: Exit Sub
    If cboRegion.ListIndex = -1 Then MsgBox "Pick a region": Exit Sub
    If Not IsDate(txtDate.Value) Then MsgBox "Enter a valid date": Exit Sub

    Set ws = ThisWorkbook.Sheets("Data")
    r = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
    ws.Cells(r, 1).Value = CDate(txtDate.Value)
    ws.Cells(r, 2).Value = Trim(txtName.Value)
    ws.Cells(r, 3).Value = cboRegion.Value
    ws.Cells(r, 4).Value = CDbl(txtAmount.Value)

    txtName.Value = "": txtAmount.Value = "": cboRegion.ListIndex = -1
    txtName.SetFocus
End Sub

Private Sub btnClose_Click()
    Unload Me
End Sub

4. Open it with a button

Sub ShowEntryForm()
    frmEntry.Show
End Sub

Insert a shape on the sheet, right-click → Assign Macro → ShowEntryForm. Save as .xlsm.

New to VBA? Start with the free Excel VBA course.