Your First UserForm

Why bother with a form?

A worksheet is a wonderful grid and a terrible input screen. People type into the wrong row, skip the mandatory column, paste text where a date belongs. A UserForm is a dialog you design yourself: it asks for exactly what you need, checks it, and writes it to the right place.

A form cannot be pasted in — it has to be drawn

This is the one thing in VBA no code block can give you. A UserForm is a design object stored in the workbook, so the layout is drawn in the editor by hand. Follow these steps exactly and the code below works unchanged.

The click sequence

• 1. Alt+F11, then Insert → UserForm. A grey form and the Toolbox appear; if the Toolbox is missing, View → Toolbox.
• 2. F4 for Properties. With the form selected, set (Name) to frmEntry and Caption to New expense. Name it now — see the trap below.
• 3. With the Label tool (the letter A) draw three labels down the left. Set their Caption to Date, What for, Amount.
• 4. With the TextBox tool (ab|) draw three boxes beside them, named txtDate, txtWhat, txtAmount, top to bottom.
• 5. With the CommandButton tool draw two buttons at the bottom. Left: (Name) cmdOK, Caption OK, Default True. Right: cmdCancel, Cancel, Cancel True.
• 6. Double-click the grey background. The form's code module opens; delete the stub and paste the first block below.
• 7. Insert → Module, and paste the second block there.
• 8. Put the cursor in ShowEntryForm, press F5, and save as .xlsm — .xlsx discards all of it.

The code behind the buttons

Default = True makes Enter press OK, Cancel = True makes Escape press Cancel. This block belongs in the form's own module, never in a normal one:

Option Explicit

Private Sub UserForm_Initialize()
    Me.txtDate.Value = Format$(Date, "dd.mm.yyyy")
    Me.txtWhat.Value = ""
    Me.txtAmount.Value = ""
    Me.txtWhat.SetFocus
End Sub

Private Sub cmdOK_Click()
    Dim ws As Worksheet
    Dim nextRow As Long

    If Not IsDate(Me.txtDate.Value) Then
        MsgBox "That is not a date I can read.", vbExclamation
        Me.txtDate.SetFocus
        Exit Sub
    End If

    If Len(Trim$(Me.txtWhat.Value)) = 0 Then
        MsgBox "Please say what the expense was for.", vbExclamation
        Me.txtWhat.SetFocus
        Exit Sub
    End If

    If Not IsNumeric(Me.txtAmount.Value) Then
        MsgBox "The amount has to be a number.", vbExclamation
        Me.txtAmount.SetFocus
        Exit Sub
    End If

    Set ws = EntrySheet()
    nextRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

    ws.Cells(nextRow, 1).Value = CDate(Me.txtDate.Value)
    ws.Cells(nextRow, 2).Value = Trim$(Me.txtWhat.Value)
    ws.Cells(nextRow, 3).Value = CDbl(Me.txtAmount.Value)
    ws.Cells(nextRow, 1).NumberFormat = "dd.mm.yyyy"

    Me.txtWhat.Value = ""
    Me.txtAmount.Value = ""
    Me.txtWhat.SetFocus
End Sub

Private Sub cmdCancel_Click()
    Unload Me
End Sub

The launcher and the target sheet

The second block goes in the standard module. It creates the Expenses sheet and its headers the first time they are needed, so the form never writes into thin air:

Option Explicit

Sub ShowEntryForm()
    EntrySheet
    frmEntry.Show
End Sub

Public Function EntrySheet() As Worksheet
    Dim ws As Worksheet
    Dim found As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "Expenses" Then Set found = ws
    Next ws

    If found Is Nothing Then
        Set found = ThisWorkbook.Worksheets.Add
        found.Name = "Expenses"
        found.Range("A1:C1").Value = Array("Date", "What for", "Amount")
        found.Range("A1:C1").Font.Bold = True
        found.Columns("A:C").ColumnWidth = 18
    End If

    Set EntrySheet = found
End Function

Line by line

• UserForm_Initialize runs once, before the form appears: it pre-fills today's date and puts the cursor in the first field to be typed in.
• Me is the form itself, so Me.txtAmount is the box you drew. Typing Me. lists every control — another reason for good names.
• Each Exit Sub in cmdOK_Click is a gate: complain, put the cursor back in the offending box, write nothing.
• ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1 finds the first free row from the bottom up, so entries never overwrite one another.
• CDate and CDbl convert on the way in. Everything a TextBox hands you is text, and text written to a cell stays text: the row looks right and refuses to sum.
• After a save the boxes are cleared rather than the form closed, so it becomes an add-many-entries screen. Unload Me closes and discards it.

The trap that costs an hour

Event procedures are wired to controls by name and nothing else. Write the code first, rename the button afterwards, and the click does nothing: no error, no message, just a dead button, while the old procedure sits there looking correct. Copying a control does the same, since the copy is CommandButton1 again. Name every control as you draw it.

' The button was called CommandButton1 when this was written.
' You renamed the button to cmdOK afterwards, so this now runs for nobody.
Private Sub CommandButton1_Click()
    MsgBox "Never called again"
End Sub

' The event is wired by NAME. This is the one Excel looks for.
Private Sub cmdOK_Click()
    MsgBox "Saved"
End Sub

How it goes on

You have a working form, but a deliberately plain one. The next lessons add what makes it feel finished: the full set of controls and tab order, Initialize filling drop-downs from a sheet, ComboBoxes and ListBoxes, checkboxes and option groups, one validation function replacing the three If gates above, and the proper way to write results and close.