Worksheet Event Auditor: Find the Invisible Code Running Every Time You Click

TL;DR: You open an inherited macro-enabled workbook and something keeps reformatting your numbers when you type, hiding rows when you select a cell, and recalculating when you switch tabs — but you can’t find the code doing it. This macro reads the VBA project, finds every Worksheet_Change, Worksheet_SelectionChange, Worksheet_Activate, and other event handler on every sheet, and writes them to a report sheet with line counts. No more opening 30 sheet modules one by one in the VBA editor.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)

• A macro-enabled workbook (.xlsm or .xlsb) with event handlers you want to audit

• Trust access to the VBA project object model — the macro reads your VBA code, so this setting must be enabled:

  1. File → Options → Trust Center → Trust Center Settings
  2. Macro Settings → Check “Trust access to the VBA project object model”
  3. Save, close, and reopen the workbook before running

What the macro does NOT do:

• It does not modify or delete any VBA code. Read-only.
• It does not list standard module macros — only event handlers in sheet modules and the ThisWorkbook module.
• It does not show you what the code does — only that it exists, where it lives, and how many lines it is.

Limitations:

• Requires VBA project trust access (see above). If disabled, the macro shows a clear error message with setup instructions.
• Cannot read protected VBA projects (password-locked).
• Only detects the most common 17 event patterns (see list below). Custom event handlers named with non-standard prefixes won’t be caught.
• Line counts are approximate — they count from Sub to End Sub inclusive, including blank lines and comments.

#The Macro

Option Explicit

Sub AuditEventHandlers()
    ' ── Worksheet Event Auditor ──────────────────────
    ' Lists every worksheet event handler in the VBA
    ' project. Finds Worksheet_Change, Activate,
    ' SelectionChange, BeforeDoubleClick, and 13 more
    ' event patterns across all sheet modules and
    ' ThisWorkbook. Outputs to "Event-Handlers" sheet.
    '
    ' Prerequisite: Trust access to VBA project object
    ' model (File → Options → Trust Center → Trust
    ' Center Settings → Macro Settings).
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "Event-Handlers"

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim vbProj As Object, vbComp As Object
    Dim codeText As String, lines() As String
    Dim rpt As Worksheet, outRow As Long
    Dim i As Long, j As Long, handlerCount As Long
    Dim sheetCount As Long, depth As Long
    Dim eventType As String, handlerName As String
    Dim sheetName As String, foundOnSheet As Boolean

    ' ── Event patterns the macro searches for ──────────
    Dim eventPatterns As Variant, pat As Variant
    eventPatterns = Array( _
        "Worksheet_Change", "Worksheet_Activate", _
        "Worksheet_Deactivate", "Worksheet_SelectionChange", _
        "Worksheet_BeforeDoubleClick", _
        "Worksheet_BeforeRightClick", _
        "Worksheet_Calculate", "Worksheet_FollowHyperlink", _
        "Worksheet_PivotTableUpdate", _
        "Workbook_Open", "Workbook_BeforeClose", _
        "Workbook_BeforeSave", "Workbook_SheetChange", _
        "Workbook_SheetActivate", "Workbook_SheetDeactivate", _
        "Workbook_NewSheet", "Workbook_BeforePrint")

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Check VBA project access ───────────────
    On Error Resume Next
    Set vbProj = ThisWorkbook.VBProject
    If vbProj Is Nothing Then
        MsgBox "Cannot access VBA project." & vbCrLf & vbCrLf & _
               "Enable: File → Options → Trust Center → " & _
               "Trust Center Settings → Macro Settings → " & _
               "Check 'Trust access to the VBA project " & _
               "object model'." & vbCrLf & vbCrLf & _
               "Then save, close, and reopen this workbook.", _
               vbExclamation, "VBA Access Required"
        GoTo CleanUp
    End If
    On Error GoTo CleanUp

    ' ── Step 2: Create report sheet ────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set rpt = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    rpt.Name = OUT_SHEET

    rpt.Range("A1:E1").Value = Array( _
        "Sheet/Module", "Event Type", "Handler Name", _
        "Lines", "Module Type")
    rpt.Range("A1:E1").Font.Bold = True
    rpt.Range("A1:E1").Interior.Color = RGB(50, 50, 50)
    rpt.Range("A1:E1").Font.Color = vbWhite

    outRow = 2
    handlerCount = 0
    sheetCount = 0

    ' ── Step 3: Scan each VBA component ────────────────
    For Each vbComp In vbProj.VBComponents
        ' Only check sheet modules and ThisWorkbook
        If vbComp.Type = 100 Then  ' vbext_ct_Document
            On Error Resume Next
            sheetName = vbComp.Properties("Name").Value
            On Error GoTo CleanUp
        ElseIf vbComp.Name = "ThisWorkbook" Then
            sheetName = "ThisWorkbook"
        Else
            GoTo NextComp  ' Skip standard modules, class modules
        End If

        ' Read the full code text
        On Error Resume Next
        codeText = vbComp.CodeModule.Lines( _
            1, vbComp.CodeModule.CountOfLines)
        On Error GoTo CleanUp
        If Len(codeText) = 0 Then GoTo NextComp

        lines = Split(codeText, vbCrLf)
        foundOnSheet = False

        ' ── Step 4: Search for event handler Subs ───────
        For i = LBound(lines) To UBound(lines)
            Dim ln As String
            ln = Trim(lines(i))

            For Each pat In eventPatterns
                If InStr(1, ln, "Sub " & CStr(pat), _
                         vbTextCompare) = 1 Then
                    eventType = CStr(pat)
                    ' Strip "Sub " prefix for display
                    handlerName = Trim(Replace(ln, "Sub ", "", , 1, _
                        vbTextCompare))

                    ' Find matching End Sub (track depth for
                    ' nested Subs — rare but possible)
                    depth = 1
                    For j = i + 1 To UBound(lines)
                        If InStr(1, lines(j), "Sub ", _
                                 vbTextCompare) > 0 And _
                           InStr(1, lines(j), "End Sub", _
                                 vbTextCompare) = 0 Then
                            depth = depth + 1
                        ElseIf InStr(1, lines(j), "End Sub", _
                                     vbTextCompare) > 0 Then
                            depth = depth - 1
                            If depth = 0 Then Exit For
                        End If
                    Next j

                    ' Write to report
                    rpt.Cells(outRow, 1).Value = sheetName
                    rpt.Cells(outRow, 2).Value = eventType
                    rpt.Cells(outRow, 3).Value = handlerName
                    rpt.Cells(outRow, 4).Value = j - i + 1

                    If vbComp.Type = 100 Then
                        rpt.Cells(outRow, 5).Value = "Sheet Module"
                    Else
                        rpt.Cells(outRow, 5).Value = "Workbook Module"
                    End If

                    ' Highlight performance-critical handlers
                    If eventType = "Worksheet_Change" Or _
                       eventType = "Worksheet_SelectionChange" Then
                        rpt.Cells(outRow, 2).Interior.Color = _
                            RGB(254, 202, 202)
                    End If

                    handlerCount = handlerCount + 1
                    foundOnSheet = True
                    outRow = outRow + 1
                    i = j  ' Skip past the handler body
                    Exit For
                End If
            Next pat
        Next i

        If foundOnSheet Then sheetCount = sheetCount + 1
NextComp:
    Next vbComp

    ' ── Step 5: Format and sort report ─────────────────
    If handlerCount > 0 Then
        rpt.Columns("A:E").AutoFit
        rpt.Columns("C").ColumnWidth = 45
        rpt.Range("A1").Select
        ActiveWindow.FreezePanes = True

        ' Sort: workbook module first, then sheet modules
        rpt.Sort.SortFields.Clear
        rpt.Sort.SortFields.Add _
            Key:=rpt.Range("E2:E" & outRow - 1), _
            SortOn:=xlSortOnValues, Order:=xlAscending
        rpt.Sort.SortFields.Add _
            Key:=rpt.Range("A2:A" & outRow - 1), _
            SortOn:=xlSortOnValues, Order:=xlAscending
        With rpt.Sort
            .SetRange rpt.Range("A1:E" & outRow - 1)
            .Header = xlYes
            .Apply
        End With
    End If

    ' ── Step 6: Build summary ──────────────────────────
    Dim tally As Object, key As Variant, eventSummary As String
    If handlerCount = 0 Then
        MsgBox "No event handlers found. This workbook has " & _
               "no Worksheet_Change, Worksheet_Activate, " & _
               "Worksheet_SelectionChange, or other event " & _
               "code in its sheet modules.", vbInformation, _
               "No Events Found"
    Else
        ' Count by event type for a compact summary
        Set tally = CreateObject("Scripting.Dictionary")
        For i = 2 To outRow - 1
            key = rpt.Cells(i, 2).Value
            If tally.Exists(key) Then
                tally(key) = tally(key) + 1
            Else
                tally.Add key, 1
            End If
        Next i

        eventSummary = ""
        For Each key In tally.Keys
            eventSummary = eventSummary & "  " & key & ": " & _
                tally(key) & vbCrLf
        Next key

        MsgBox "Found " & handlerCount & " event handler(s) " & _
               "across " & sheetCount & " module(s)." & _
               vbCrLf & vbCrLf & eventSummary & vbCrLf & _
               "Report created on '" & OUT_SHEET & "' sheet. " & _
               "Sort by Lines column to find the biggest " & _
               "handlers.", vbInformation, "Event Audit Complete"
    End If

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#It reads your VBA code (not your data)

This macro doesn’t touch a single worksheet cell (other than writing the report). It talks directly to the VBA project object model via ThisWorkbook.VBProject.VBComponents. This is the same API the VBA editor uses to show you code in the IDE — the macro just automates what you’d do manually.

Because this API reads source code, it requires the “Trust access to the VBA project object model” setting. If that setting is off, the macro shows a helpful error message with step-by-step instructions to enable it — no cryptic runtime error.

#17 event patterns in one pass

eventPatterns = Array( _
    "Worksheet_Change", "Worksheet_Activate", _
    "Worksheet_Deactivate", "Worksheet_SelectionChange", _
    ...
    "Workbook_BeforePrint")

The macro searches for 17 event handler patterns across two domains:

• Sheet-level events (9): Change, Activate, Deactivate, SelectionChange, BeforeDoubleClick, BeforeRightClick, Calculate, FollowHyperlink, PivotTableUpdate. These fire on individual sheets.

• Workbook-level events (8): Open, BeforeClose, BeforeSave, SheetChange, SheetActivate, SheetDeactivate, NewSheet, BeforePrint. These fire across the entire workbook from the ThisWorkbook module.

The search is case-insensitive (vbTextCompare), so worksheet_change and Worksheet_Change both match. It looks for Sub followed by the pattern name at the start of a trimmed line — this avoids matching comments or variable names that happen to contain the event name.

#Depth tracking for nested Subs

A Worksheet_Change handler might be 5 lines long. Or it might be 200 lines with nested helper Subs inside it. The macro uses a depth counter:

depth = 1
For j = i + 1 To UBound(lines)
    If InStr(1, lines(j), "Sub ", vbTextCompare) > 0 And _
       InStr(1, lines(j), "End Sub", vbTextCompare) = 0 Then
        depth = depth + 1
    ElseIf InStr(1, lines(j), "End Sub", vbTextCompare) > 0 Then
        depth = depth - 1
        If depth = 0 Then Exit For
    End If
Next j

Every Sub (that isn’t End Sub) increments the depth. Every End Sub decrements it. When depth hits zero, we’ve found the matching End Sub for the handler we’re tracking — even if it has nested Subs inside it.

Without this, the line count for a handler containing a nested helper Sub would be wildly wrong. With it, you get an accurate End Sub - Sub + 1 count every time.

#Change and SelectionChange get a red flag

If eventType = "Worksheet_Change" Or _
   eventType = "Worksheet_SelectionChange" Then
    rpt.Cells(outRow, 2).Interior.Color = RGB(254, 202, 202)
End If

Worksheet_Change fires every time a user types a value. SelectionChange fires every time they move the cursor. These are the two most dangerous event types because they run on every keystroke and every cell navigation. A 200-line Worksheet_Change that reformats data, recalculates ranges, and validates inputs is the #1 cause of sluggish workbooks.

The macro highlights these in light red so they jump out visually. If your report shows a red Worksheet_Change with 150+ lines of code on Sched-C, that’s almost certainly the culprit behind the auto-formatting behavior.

#VBA access check with a real error message

On Error Resume Next
Set vbProj = ThisWorkbook.VBProject
If vbProj Is Nothing Then
    MsgBox "Cannot access VBA project." & vbCrLf & vbCrLf & _
           "Enable: File → Options → Trust Center → ..." & _
           vbCrLf & vbCrLf & _
           "Then save, close, and reopen this workbook.", _
           vbExclamation, "VBA Access Required"
    GoTo CleanUp
End If

Most VBA macros that use VBProject fail silently or show a generic “Object variable not set” error. This macro detects the exact failure condition (the VBProject reference returns Nothing when access is denied) and shows step-by-step instructions to fix it. No Googling required.

#Why the report goes at the BEGINNING

Set rpt = ThisWorkbook.Worksheets.Add( _
    Before:=ThisWorkbook.Worksheets(1))

Unlike most report-generating macros (which put output at the end), the event auditor puts its report as the first sheet. The reason: when you’re hunting for invisible code, you want the answer front and center the moment you open the workbook, not hidden behind 30 data tabs. The Event-Handlers sheet is the first thing you see.

#Skip standard modules — they don’t have event handlers

The macro deliberately skips vbComp.Type <> 100 and vbComp.Name <> "ThisWorkbook". Standard modules (.bas modules where you put regular Subs and Functions) and class modules can’t contain worksheet event handlers. Scanning them would produce zero results, so skipping them saves processing time and avoids a misleading “0 event handlers” report on modules that aren’t supposed to have any.

#Summary message with per-type counts

The closing MsgBox doesn’t just say “Found 8 handlers.” It breaks them down by type:

Found 8 event handler(s) across 4 module(s).

  Worksheet_Change: 3
  Worksheet_SelectionChange: 2
  Worksheet_Activate: 1
  Workbook_Open: 1
  Workbook_BeforeSave: 1

This gives you an instant read on the workbook’s event architecture. Three Change handlers means three sheets have code that fires on every keystroke. That’s worth investigating even before you look at the report sheet.

#Adapt It