Skip to content
Tutorials Center
My learning

Topics

Lesson 77 of 93 ยท Automation and Collaboration

VBA Events, UserForms and Controls

Make Excel react with VBA events: Worksheet_Change, Workbook_Open, BeforeSave, UserForms, form controls versus ActiveX, buttons, InputBox, MsgBox and auto-run macros.

So far macros ran when you told them to. Events turn that around: Excel runs your code when something happens, such as a cell changing, a sheet being activated, or a workbook opening. UserForms and controls give your macros a proper interface with buttons, boxes and drop-downs instead of a bare InputBox. Together they turn a workbook into a small application.

Excel and VBA cannot run on this site, so VBA listings are read-only. The TryIts are JavaScript stand-ins that model an event dispatcher and a form with validation, so you can follow the order in which things happen. They are not VBA and do not touch Excel.

How events work

Each object (a worksheet, the workbook) announces events. VBA looks for a procedure with the exact reserved name in the right place: sheet events live in that sheet's code module, workbook events in ThisWorkbook. You do not insert a module for them: double-click the sheet (or ThisWorkbook) in the Project Explorer, then pick the object and the event from the two drop-downs at the top of the code window and the editor writes the correct signature.

Event Where Fires when
Worksheet_Change(ByVal Target As Range) a sheet a cell value is changed by the user or code
Worksheet_SelectionChange(ByVal Target As Range) a sheet the selection moves
Worksheet_Activate / Worksheet_Deactivate a sheet you switch to or from the sheet
Worksheet_BeforeDoubleClick, Worksheet_BeforeRightClick a sheet before the default action (Cancel = True stops it)
Workbook_Open ThisWorkbook the file opens (after macros are enabled)
Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook before saving; set Cancel = True to stop it
Workbook_AfterSave ThisWorkbook after a successful save
Workbook_NewSheet(ByVal Sh As Object) ThisWorkbook a sheet is added
Workbook_BeforeClose(Cancel As Boolean) ThisWorkbook before the file closes

Worksheet_Change, safely

The classic first event reacts to edits in a specific range. Two guards matter. First, check that the changed cell is one you care about with Intersect. Second, switch events off while your code changes cells, or your own write fires the event again and loops until Excel runs out of stack.

VB
' In the code module of the sheet "Orders"
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("C2:C500")) Is Nothing Then Exit Sub   ' not our column

    Application.EnableEvents = False          ' stop re-triggering ourselves
    On Error GoTo Done                        ' make sure events come back
    Target.Offset(0, 1).Value = Now           ' time stamp next to the edit
    Target.Value = UCase(Target.Value)        ' this write would loop without the guard
Done:
    Application.EnableEvents = True
End Sub

Target is the range that changed (it can be many cells, for example after a paste), so use For Each c In Target if you need to handle each. Formula recalculation does not raise Worksheet_Change; use Worksheet_Calculate for that. Worksheet_SelectionChange is commonly used to highlight the active row, and it fires often, so keep it fast.

Auto-run on open

Workbook_Open is the supported way to run code when a file opens:

VB
' In ThisWorkbook
Private Sub Workbook_Open()
    Worksheets("Dashboard").Activate
    Worksheets("Dashboard").Range("B2").Value = "Opened " & Format(Now, "yyyy-mm-dd hh:nn")
End Sub

A procedure named Auto_Open in a standard module also runs, but Workbook_Open is the modern choice. Nothing runs if the user opens the file with macros disabled, and holding Shift while opening skips the event, so never rely on it for security.

Run it: an event dispatcher stand-in

The stand-in keeps a list of handlers per event name and shows the two ideas that matter: only the right range triggers the handler, and EnableEvents = False prevents the infinite loop.

JS Worksheet_Change with an EnableEvents guard

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Messages and input

MsgBox shows a message and returns the button clicked. InputBox asks for one value. There are two flavors of InputBox: the plain VBA function returns text (an empty string for Cancel or empty input), while Application.InputBox can demand a type and even let the user click a range:

VB
Dim rng As Range, qty As Variant
On Error Resume Next
Set rng = Application.InputBox("Select the data range", Type:=8)   ' 8 = Range
On Error GoTo 0
If rng Is Nothing Then Exit Sub                                    ' Cancel pressed

qty = Application.InputBox("Quantity?", "Order", 1, Type:=1)       ' 1 = number
If qty = False Then Exit Sub                                       ' Cancel returns False

MsgBox "Total: " & qty * 2.5, vbInformation + vbOKOnly, "Result"

For MsgBox, add button and icon constants with +: vbYesNoCancel + vbExclamation. Compare the answer with vbYes, vbNo, vbCancel.

Controls on the sheet: form controls and ActiveX

Open Developer > Insert. There are two sets of controls:

Form controls ActiveX controls
Look and settings Simple, fixed Rich: fonts, colors, many properties and events
Code You assign a macro to the control Code lives in the sheet's module (CommandButton1_Click)
Works on Mac / Excel for the web Mostly yes No, Windows desktop only
Reliability Very stable Can break after updates or when files move between machines

Choose form controls unless you need ActiveX-only features. To assign a macro to a form button, draw it, and the Assign Macro dialog opens; pick the Sub (it must take no arguments). You can also right-click any shape, picture or icon and choose Assign Macro, which makes good-looking buttons. A drop-down (combo box) form control uses an Input range and a Cell link that receives the chosen position, a handy partner for INDEX.

UserForms

A UserForm is a custom dialog. In the VBE choose Insert > UserForm, then drag controls from the Toolbox: Label, TextBox, ComboBox, ListBox, CheckBox, OptionButton and CommandButton. In the Properties window give each a meaningful Name (txtName, cboRegion, btnSave) and set the form's Caption.

VB
' Code behind the form frmOrder
Private Sub UserForm_Initialize()
    cboRegion.List = Array("North", "South", "East", "West")
    cboRegion.ListIndex = 0
End Sub

Private Sub btnSave_Click()
    If Trim(txtName.Value) = "" Then
        MsgBox "Please enter a name.", vbExclamation
        txtName.SetFocus
        Exit Sub
    End If
    If Not IsNumeric(txtQty.Value) Then
        MsgBox "Quantity must be a number.", vbExclamation
        Exit Sub
    End If

    Dim r As Long
    With Worksheets("Orders")
        r = .Cells(.Rows.Count, "A").End(xlUp).Row + 1     ' next empty row
        .Cells(r, 1).Value = txtName.Value
        .Cells(r, 2).Value = cboRegion.Value
        .Cells(r, 3).Value = CDbl(txtQty.Value)
    End With
    Unload Me                                              ' close the form
End Sub

Private Sub btnCancel_Click()
    Unload Me
End Sub

Show the form from any Sub with frmOrder.Show. A form is modal by default: Excel waits until it closes. Use .Show vbModeless for a form that stays open while the user works in the sheet. Always validate input before writing it to cells, and read controls through .Value. For the Built-in Data Form, which needs no code, see Checkboxes, Hyperlinks and Data Entry Forms.

Run it: a form with validation

JS UserForm save logic with validation

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Security and good habits

  • Events and auto-run code are exactly what macro malware uses, which is why Excel disables macros from the internet by default. Sign your own macros or store trusted files in a Trusted Location.
  • Keep event code short and fast; it runs on every change.
  • Always put Application.EnableEvents = True in the exit path, error handler included. If events stop working in Excel, run Application.EnableEvents = True from the Immediate window.
  • Name your controls and procedures clearly, and comment why a handler exists.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why set Application.EnableEvents = False inside a Worksheet_Change procedure that edits cells?

    Writing to a cell raises Worksheet_Change again. Turning events off while you write prevents recursion; turn them back on in the exit path.
  2. 2. Where must a Workbook_Open procedure be placed?

    Workbook events live in ThisWorkbook and sheet events in the sheet's own module. The name and signature must match exactly.
  3. 3. Which control type is simplest to use on Excel for Mac and the web and needs a macro assigned to it?

    Form controls are lightweight and portable; you assign a macro to them. ActiveX controls are Windows desktop only and carry their own code.
  4. 4. What does Application.InputBox("Pick cells", Type:=8) let the user do?

    Type 8 returns a Range object chosen by clicking or dragging in the sheet. Cancel leaves the object variable as Nothing, so test for it.

Summary

  • Events run code automatically; sheet events belong in the sheet's module and workbook events in ThisWorkbook, with exact names.
  • Guard Worksheet_Change with Intersect, and wrap edits in Application.EnableEvents = False ... True.
  • Workbook_Open, Workbook_BeforeSave and Workbook_NewSheet cover start-up, save and sheet creation.
  • MsgBox returns the button clicked; Application.InputBox can require numbers or ranges.
  • Form controls take an assigned macro and travel well; ActiveX has more options but is Windows only; shapes can also be buttons.
  • A UserForm needs named controls, input validation and Unload Me; show it with .Show.

Next: VBA for Charts, PivotTables and User-Defined Functions.