Worksheet Events

Code that runs itself

Everything so far has waited for a button. An event is different: Excel calls your code automatically when something happens. The most useful of them is Worksheet_Change, which fires every time a cell on that sheet is edited. It is how a line total appears the instant somebody types a quantity, and how you get an audit stamp nobody has to remember to write.

Step one: build the sheet

Put this in a normal module (Insert → Module) and run it. It creates an Orders sheet with the six columns the event code expects, so you can try the whole thing in a blank workbook:

Option Explicit

Sub SetUpOrdersSheet()
    Dim ws As Worksheet
    Dim found As Worksheet

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

    If found Is Nothing Then
        Set found = ThisWorkbook.Worksheets.Add
        found.Name = "Orders"
    End If

    found.Cells.Clear
    found.Range("A1:F1").Value = Array("Date", "Item", "Qty", _
                                       "Unit price", "Total", "Last edited")
    found.Range("A1:F1").Font.Bold = True
    found.Range("A2").Value = Date
    found.Range("B2").Value = "Sample item"
    found.Columns("A:F").ColumnWidth = 15
    found.Activate
    found.Range("C2").Select

    MsgBox "Sheet 'Orders' is ready. Now paste the event code into " & _
           "the module of THIS sheet, then type a quantity in C2 " & _
           "and a price in D2.", vbInformation
End Sub

Step two: the event has to live in the sheet

Sheet events only work in that sheet's own module. In the Project Explorer on the left, double-click Sheet… (Orders) — not the module you just used. An empty code window opens; paste the block below into it. Nothing is run by hand from now on: type a quantity in C2 and a price in D2 and watch columns E and F fill themselves.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim watched As Range
    Dim cell As Range
    Dim qty As Variant
    Dim price As Variant

    Set watched = Intersect(Target, Me.Range("C2:D10000"))
    If watched Is Nothing Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False

    For Each cell In watched
        qty = Me.Cells(cell.Row, "C").Value
        price = Me.Cells(cell.Row, "D").Value

        If Len(qty) > 0 And Len(price) > 0 _
           And IsNumeric(qty) And IsNumeric(price) Then
            Me.Cells(cell.Row, "E").Value = qty * price
            Me.Cells(cell.Row, "F").Value = Now
            Me.Cells(cell.Row, "F").NumberFormat = "dd.mm.yyyy hh:mm"
        Else
            Me.Cells(cell.Row, "E").Resize(1, 2).ClearContents
        End If
    Next cell

CleanUp:
    Application.EnableEvents = True
End Sub

Line by line

• Target is the range that changed. It is not always one cell — a paste, a fill handle or a delete hands you hundreds at once, which is why the code never touches Target.Value directly.
• Intersect(Target, Me.Range("C2:D10000")) asks what the edit and the columns you care about have in common, and returns Nothing if there is no overlap. The Exit Sub after it is the most important line here: the event fires on every edit anywhere on the sheet, so the first job is to leave again.
• Me is the sheet the code lives in, so the macro cannot write to whatever sheet happens to be active.
• Len(qty) > 0 comes before IsNumeric(qty) on purpose: an empty cell returns Empty, and IsNumeric(Empty) is True. Without the length test, clearing a cell would write a zero.
• The Else branch clears E and F, so deleting a quantity removes the stale total instead of leaving a wrong one.
• On Error GoTo CleanUp with the CleanUp: label means every ending, success or failure, passes through the line that switches events back on.

The trap: the workbook that stops reacting

If your event code writes to the sheet, that write is itself a change, which fires the event again, which writes again, until Excel gives up with an out-of-stack error. Application.EnableEvents = False prevents that — and then becomes the trap. Unlike ScreenUpdating, Excel never switches events back on by itself, and the setting belongs to the whole application, not to one workbook. A macro that fails before restoring it leaves every event in every open workbook dead until you quit Excel. Hence the CleanUp label, and this rescue macro in your personal workbook:

Sub EventsOn()
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

The other sheet events

Worksheet_SelectionChange fires when the cursor moves — useful for highlighting the active row, but it runs constantly, so keep it tiny. Worksheet_BeforeDoubleClick and Worksheet_BeforeRightClick both hand you a Cancel argument: set it to True and Excel's normal behaviour is suppressed, which turns a double-click on a row into open this record in a form.

How it goes on

This lesson automates one sheet. The next moves up a level to workbook events, such as running something on open or refusing to save while a field is empty. From there the course leaves single files behind: looping through a folder with Dir, consolidating many workbooks into one dataset, building the report sheet itself, exporting it to PDF, sending it through Outlook, and finally making the whole chain fast enough to run unattended.