Automating Real Tasks with VBA

Excel Advanced

Chapter 8 · Automating Real Tasks with VBA: UserForms and Event Handlers

Every Sub procedure in Chapter 7 had to be manually triggered — a shortcut key, the Macros list, a shape. This chapter covers two features that break that pattern: a UserForm gives VBA a real input dialog instead of relying on cells alone, and an event handler makes code run automatically in response to something happening, with no manual trigger at all.

UserForms: Building a Custom Input Dialog

In the VBA Editor, Insert → UserForm creates a blank, resizable dialog window design surface, alongside a Toolbox palette of draggable controls — TextBox, Label, CommandButton, ComboBox, and more. Drag a Label and a TextBox onto the form for an input field, and a CommandButton for a Submit action.

Every control has a Properties window (F4), where its (Name) property is set — exactly the same "give it a clear, reusable name" discipline as Chapter 7's variable declarations, just applied to form controls instead of code:

The TextBox's (Name): txtAmount
The CommandButton's (Name): cmdSubmit

Writing Code Behind a Button

Double-clicking cmdSubmit on the form's design surface jumps straight into its Click event code stub, already correctly named and ready to fill in:

Private Sub cmdSubmit_Click()
    Dim lastRow As Integer
    lastRow = Worksheets("Transactions").Cells(Worksheets("Transactions").Rows.Count, 1).End(xlUp).Row + 1

    Worksheets("Transactions").Cells(lastRow, 1).Value = txtAmount.Text

    Unload Me
End Sub

txtAmount.Text reads whatever the user typed into that TextBox — the form's own equivalent of reading a cell's .Value, from Chapter 7. Unload Me closes the form once its job is done; Me refers to the form itself from code running inside it.

Showing the Form

A UserForm doesn't appear on its own — a small triggering Sub, placed in a regular module and run the normal way (a shortcut, a worksheet button), displays it:

Sub ShowEntryForm()
    UserForm1.Show
End Sub

Event Handlers: Code That Runs Automatically

Everything so far — including a UserForm's own button click — is still triggered by a direct action. An event handler goes one step further: it runs automatically whenever Excel itself does something specific, with no shortcut, button, or Macros-list entry involved at all. Two of the most useful live in specific, already-existing modules in the VBA Editor's Project pane:

Workbook_Open — placed inside the ThisWorkbook module, runs automatically the instant the workbook finishes opening.
Worksheet_Change — placed inside a specific sheet's own module (e.g. Sheet1 (Transactions) in the Project pane), runs automatically every time any cell on that specific worksheet changes.

' In the ThisWorkbook module:
Private Sub Workbook_Open()
    MsgBox "Welcome back — remember to check the Dashboard sheet."
End Sub

A Worked Worksheet_Change Example

Worksheet_Change receives a built-in parameter, Target, identifying exactly which cell (or range) just changed — letting the handler react only when a specific relevant column is edited:

' In the Transactions worksheet's own module:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Then   ' column C — the Amount column
        If Target.Value < 0 Then
            Application.EnableEvents = False
            Target.Offset(0, 1).Value = "Check: Negative Amount"
            Application.EnableEvents = True
        End If
    End If
End Sub

Unlike Excel Advanced Chapter 2's Data Validation, which prevents an invalid entry from being typed in the first place, Worksheet_Change reacts after the fact — useful for logic too involved for Data Validation's own List/Whole Number/Decimal rule types, at the cost of only catching the problem once it's already been entered.

Mechanism	When it runs
A normal Sub (Chapter 7)	Only when manually triggered — Macros list, shortcut key, a shape
A UserForm button's Click event	When the user clicks that specific button on a shown form
Workbook_Open / Worksheet_Change	Automatically, whenever Excel itself opens the file or changes a cell — no manual trigger at all
Data Validation (Chapter 2)	Prevents invalid input before it's ever entered — no VBA involved

A UserForm's control names deserve the same discipline as a variable's name

txtAmount and cmdSubmit read clearly in code specifically because their names describe what they are and what they hold — the exact same reasoning behind Chapter 7's own variable-naming discipline, just extended to form controls. A form full of controls still named their defaults (TextBox1, CommandButton1) works identically, but becomes genuinely harder to read back later once a form has more than one or two fields.

A Worksheet_Change handler that writes to its own sheet can re-trigger itself

If a Worksheet_Change procedure itself writes to a cell on the SAME worksheet it's watching (like this chapter's own example, writing a flag into the neighbouring column), that write is itself a change to the worksheet — which fires Worksheet_Change all over again, redundantly at best and as a runaway loop at worst if the second firing writes somewhere that triggers a third. Wrapping the handler's own writes in Application.EnableEvents = False before, and = True immediately after, tells Excel to temporarily ignore any changes the code itself causes, without disabling the *next* genuine user edit.

Hands-On Exercises

Exercise 1

Design a UserForm with a TextBox named txtCategory and a CommandButton named cmdAdd. Write the cmdAdd_Click event so that clicking it appends whatever text is in txtCategory to the next empty row of column A on a worksheet named "Categories," then closes the form.

📄 View solution

Exercise 2

Write a Workbook_Open event handler that automatically selects a worksheet named "Dashboard" and displays a message box reading "Dashboard refreshed and ready" every time the workbook is opened. Explain which specific module this code must be placed in for it to run automatically, and why placing it in a regular module instead would not work.

📄 View solution

Exercise 3

A Worksheet_Change handler on a "Transactions" sheet writes a warning into column D whenever column C's value exceeds 1000. A colleague reports Excel becomes unresponsive briefly every time they enter a large value. Diagnose exactly why this happens and rewrite the handler to fix it.

📄 View solution

Chapter 8 Quick Reference

Insert → UserForm — a custom dialog; drag controls from the Toolbox, name each one clearly via the Properties window (F4)
Double-click a control in form design view to jump to its event code stub (e.g. cmdSubmit_Click)
UserForm1.Show displays a form; Unload Me closes it from inside its own code
Workbook_Open (in ThisWorkbook) and Worksheet_Change (in a specific sheet's module) run automatically — no manual trigger
Target — Worksheet_Change's built-in parameter identifying exactly which cell changed
Wrap a Worksheet_Change handler's own writes in Application.EnableEvents = False / True to prevent it re-triggering itself