Building a VBA-Powered Workbook Change Log: Automatically Capture, Timestamp, and Store Every Cell Edit with User Attribution for Auditable Enterprise Spreadsheets

Introduction

Picture this: it's 10 AM on a Tuesday, your CFO is staring at a quarterly forecast model, and a number that was $4.2 million yesterday is now $3.8 million. Nobody admits to changing it. The file has no version history. You spend two hours hunting through email threads trying to reconstruct what happened. This scenario plays out in finance departments, operations teams, and compliance functions every single week — and the painful truth is that Excel, by default, keeps no record of what changed, who changed it, or when.

Enterprise environments need more than just a working spreadsheet. They need auditable spreadsheets — workbooks where every cell edit leaves a paper trail that can survive a compliance review, an internal investigation, or a simple "wait, who changed that?" conversation. Excel's built-in Track Changes feature is too limited and too easy to disable. What you actually need is a change log baked directly into the workbook using VBA — one that captures the old value, the new value, the cell address, the sheet name, a timestamp, and the Windows username of whoever made the edit, and stores all of that in a dedicated log sheet that users cannot accidentally clear.

By the end of this lesson, you will have built a production-grade change logging system from scratch. We'll cover the event architecture, the data model for the log, performance considerations for high-volume sheets, protecting the log from tampering, and techniques for filtering and reporting on the audit trail. This is not a toy implementation — it's the kind of thing you'd actually deploy on a shared financial model.

What you'll learn:

• How to use Worksheet_Change and Workbook_SheetChange events to intercept every cell edit across an entire workbook
• How to capture old values before they're overwritten using Worksheet_SelectionChange as a pre-change snapshot mechanism
• How to build a structured, protected log sheet that stores change records with timestamps and user attribution
• How to handle multi-cell paste operations, formula entries, and delete-key events without missing records
• How to add filtering, search, and export functionality to make the audit trail genuinely useful

---

Prerequisites

This lesson is pitched at the expert level and builds on a solid VBA foundation. You should be comfortable with:

• Writing and navigating VBA code in the Visual Basic Editor (concepts covered in Getting Started with VBA Macros in Excel)
• Understanding worksheet and workbook events (covered in depth in Building a Custom VBA Event-Driven Framework)
• Working with ranges and cells programmatically (Working with Ranges, Cells, and Worksheets in VBA for Data Professionals)
• Basic error handling patterns (Error Handling and Debugging VBA Code Like a Pro)

You should also have a macro-enabled workbook (.xlsm) open and ready.

---

Understanding the Core Problem: Why Capturing Old Values is Hard

Before writing a single line of code, let's be precise about what we're actually trying to solve — because there's a subtle trap that catches almost every developer who attempts this for the first time.

When Worksheet_Change fires, the damage is already done. The cell's value has already been updated. If you try to log Target.Value as the "old value," you're logging the new value. The old value is gone.

This is the fundamental challenge of change logging in Excel. You have two options for solving it:

Option 1: The SelectionChange Snapshot — When the user selects a cell, you store its current value in a module-level variable. If they subsequently edit it, you have the "before" value already captured. This works well for single-cell edits where the user clicks a cell and then types.

Option 2: The Undo Stack — When Worksheet_Change fires, you can call Application.Undo to temporarily revert the change, read the old value, then redo the change. This is more reliable but introduces complexity and can interact badly with Excel's undo history.

We'll implement Option 1 as the primary mechanism and use a fallback strategy for cases where it fails. Understanding both approaches — and their failure modes — is what separates a robust implementation from a fragile one.

---

Designing the Log Sheet Data Model

A change log is only useful if it's queryable. Before writing event handlers, design the log sheet structure. Rushing into code first and figuring out the schema later produces a mess you'll regret.

Here's the column structure we'll build:

Column	Header	Notes
A	Log ID	Auto-incrementing integer, acts as primary key
B	Timestamp	Full date and time including seconds
C	Username	Windows login name via Environ("USERNAME")
D	Workbook	Name of the workbook (useful in multi-file scenarios)
E	Sheet	Worksheet name
F	Cell Address	Absolute address (e.g., $B$14)
G	Old Value	Value before the edit
H	New Value	Value after the edit
I	Change Type	"Edit", "Delete", "Paste", "Formula"
J	Comment	Optional field for programmatic notes

The Log ID gives you a reliable sort order and a reference you can cite in conversations ("See log entry 847"). The Change Type field lets you filter for paste operations separately from single-cell edits, which is valuable for understanding bulk data changes.

Now let's build the log sheet and its initialization routine. Create a new worksheet named "ChangeLog" and run this setup procedure:

Sub InitializeChangeLog()
    Dim wsLog As Worksheet
    Dim wsExists As Boolean
    
    ' Check if log sheet already exists
    wsExists = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name = "ChangeLog" Then
            wsExists = True
            Set wsLog = ws
            Exit For
        End If
    Next ws
    
    ' Create it if it doesn't exist
    If Not wsExists Then
        Set wsLog = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        wsLog.Name = "ChangeLog"
    End If
    
    ' Set up headers if row 1 is empty
    If wsLog.Range("A1").Value = "" Then
        Dim headers As Variant
        headers = Array("Log ID", "Timestamp", "Username", "Workbook", _
                        "Sheet", "Cell Address", "Old Value", "New Value", _
                        "Change Type", "Comment")
        Dim i As Integer
        For i = 0 To UBound(headers)
            wsLog.Cells(1, i + 1).Value = headers(i)
        Next i
        
        ' Format the header row
        With wsLog.Rows(1)
            .Font.Bold = True
            .Interior.Color = RGB(31, 73, 125)
            .Font.Color = RGB(255, 255, 255)
        End With
        
        ' Set column widths
        wsLog.Columns("A").ColumnWidth = 8    ' Log ID
        wsLog.Columns("B").ColumnWidth = 20   ' Timestamp
        wsLog.Columns("C").ColumnWidth = 15   ' Username
        wsLog.Columns("D").ColumnWidth = 20   ' Workbook
        wsLog.Columns("E").ColumnWidth = 15   ' Sheet
        wsLog.Columns("F").ColumnWidth = 12   ' Cell Address
        wsLog.Columns("G").ColumnWidth = 20   ' Old Value
        wsLog.Columns("H").ColumnWidth = 20   ' New Value
        wsLog.Columns("I").ColumnWidth = 12   ' Change Type
        wsLog.Columns("J").ColumnWidth = 30   ' Comment
        
        ' Format timestamp column
        wsLog.Columns("B").NumberFormat = "yyyy-mm-dd hh:mm:ss"
        
        ' Freeze the header row
        wsLog.Activate
        wsLog.Range("A2").Select
        ActiveWindow.FreezePanes = True
    End If
    
    ' Convert to a Table (ListObject) for filtering capability
    If wsLog.ListObjects.Count = 0 Then
        Dim tbl As ListObject
        Set tbl = wsLog.ListObjects.Add(xlSrcRange, wsLog.Range("A1:J1"), , xlYes)
        tbl.Name = "tblChangeLog"
        tbl.TableStyle = "TableStyleMedium2"
    End If
    
    MsgBox "Change Log initialized successfully.", vbInformation, "Wicked Smart Data"
End Sub

---

Building the Module-Level State for Value Snapshots

Open the VBA Editor (Alt+F11) and insert a standard module. Name it modChangeLog. This module will hold the shared state and utility functions that the event handlers call.

Option Explicit

' ============================================================
' modChangeLog - Wicked Smart Data Change Logging System
' ============================================================

' Module-level dictionary to store pre-change values
' Key: "SheetName|$A$1" format, Value: cell value as String
Private mOldValues As Object   ' Scripting.Dictionary

' Flag to prevent recursive event triggering when we write to the log
Public gLoggingActive As Boolean

' Flag to suppress logging during bulk operations
Public gSuppressLog As Boolean

' ============================================================
' Initialize the old value capture dictionary
' ============================================================
Public Sub EnsureDictionaryExists()
    If mOldValues Is Nothing Then
        Set mOldValues = CreateObject("Scripting.Dictionary")
        mOldValues.CompareMode = vbTextCompare
    End If
End Sub

' ============================================================
' Store a snapshot of values for a given range
' Called from SelectionChange before user edits
' ============================================================
Public Sub SnapshotRange(ws As Worksheet, rng As Range)
    EnsureDictionaryExists
    
    Dim cell As Range
    For Each cell In rng.Cells
        Dim key As String
        key = ws.Name & "|" & cell.Address(True, True)
        
        ' Store the value as a string to handle all data types
        If cell.HasFormula Then
            mOldValues(key) = "=" & cell.Formula  ' Prefix formulas with =
        Else
            mOldValues(key) = CStr(cell.Value)
        End If
    Next cell
End Sub

' ============================================================
' Retrieve a previously snapshotted value
' Returns "[No snapshot]" if no prior snapshot exists
' ============================================================
Public Function GetOldValue(wsName As String, cellAddress As String) As String
    EnsureDictionaryExists
    Dim key As String
    key = wsName & "|" & cellAddress
    
    If mOldValues.Exists(key) Then
        GetOldValue = mOldValues(key)
        mOldValues.Remove key  ' Clean up after retrieval
    Else
        GetOldValue = "[No snapshot]"
    End If
End Function

' ============================================================
' Write a single record to the change log
' ============================================================
Public Sub WriteLogEntry(wsName As String, _
                          cellAddress As String, _
                          oldVal As String, _
                          newVal As String, _
                          changeType As String, _
                          Optional comment As String = "")
    
    ' Don't log changes to the log sheet itself
    If wsName = "ChangeLog" Then Exit Sub
    
    ' Don't log if old and new values are identical
    If oldVal = newVal Then Exit Sub
    
    ' Don't log if suppressed (e.g., during initialization)
    If gSuppressLog Then Exit Sub
    
    Dim wsLog As Worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
    On Error GoTo 0
    
    If wsLog Is Nothing Then Exit Sub
    
    ' Find next empty row
    Dim nextRow As Long
    nextRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1
    
    ' Determine Log ID
    Dim logID As Long
    If nextRow = 2 Then
        logID = 1
    Else
        logID = wsLog.Cells(nextRow - 1, "A").Value + 1
    End If
    
    ' Prevent event recursion while writing to log
    gLoggingActive = True
    Application.EnableEvents = False
    
    With wsLog.Rows(nextRow)
        wsLog.Cells(nextRow, 1).Value = logID
        wsLog.Cells(nextRow, 2).Value = Now()
        wsLog.Cells(nextRow, 3).Value = Environ("USERNAME")
        wsLog.Cells(nextRow, 4).Value = ThisWorkbook.Name
        wsLog.Cells(nextRow, 5).Value = wsName
        wsLog.Cells(nextRow, 6).Value = cellAddress
        wsLog.Cells(nextRow, 7).Value = oldVal
        wsLog.Cells(nextRow, 8).Value = newVal
        wsLog.Cells(nextRow, 9).Value = changeType
        wsLog.Cells(nextRow, 10).Value = comment
    End With
    
    Application.EnableEvents = True
    gLoggingActive = False
    
End Sub

' ============================================================
' Get the next log row without writing (for batch operations)
' ============================================================
Public Function GetNextLogRow() As Long
    Dim wsLog As Worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
    On Error GoTo 0
    If wsLog Is Nothing Then
        GetNextLogRow = 0
        Exit Function
    End If
    GetNextLogRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1
End Function

Several design decisions here are worth explaining:

Why a Scripting.Dictionary instead of an array? Because when a user selects a multi-cell range or clicks around quickly, you need to look up "what was cell B14 on Sheet2 before this change?" efficiently. A dictionary with composite keys gives you O(1) lookup, while scanning an array would be O(n). For professional content in the learning path on efficient data structures, see VBA Arrays and Collections for Efficient Data Processing.

Why Application.EnableEvents = False while writing? Because writing to the ChangeLog sheet would itself trigger Worksheet_Change, causing infinite recursion. Disabling events prevents that. The gLoggingActive flag provides a secondary guard.

Why store formulas with an "=" prefix? To distinguish between a cell that contains the text "=SUM(A1:A10)" literally versus one that has that formula. When you store "=" & cell.Formula, you can later distinguish "this was a formula before" from "this was a literal string."

---

Writing the Event Handlers

Now for the core of the system. These go in the ThisWorkbook module — not in a standard module — because workbook-level events live there.

' ============================================================
' ThisWorkbook Module - Change Log Event Handlers
' ============================================================

Option Explicit

' ============================================================
' WORKBOOK OPEN: Initialize the logging system
' ============================================================
Private Sub Workbook_Open()
    ' Initialize the snapshot dictionary on open
    modChangeLog.EnsureDictionaryExists
    
    ' Ensure the log sheet exists
    Dim wsLog As Worksheet
    Dim found As Boolean
    found = False
    Dim ws As Worksheet
    For Each ws In Me.Worksheets
        If ws.Name = "ChangeLog" Then
            found = True
            Exit For
        End If
    Next ws
    
    If Not found Then
        MsgBox "ChangeLog sheet not found. Please run InitializeChangeLog.", _
               vbExclamation, "Change Log System"
    End If
    
    Application.EnableEvents = True
End Sub

' ============================================================
' WORKBOOK-LEVEL SELECTION CHANGE: Snapshot values before edits
' This fires whenever selection changes on ANY worksheet
' ============================================================
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
                                           ByVal Target As Range)
    ' Skip the log sheet itself
    If Sh.Name = "ChangeLog" Then Exit Sub
    
    ' Skip if logging is currently writing (prevents recursion)
    If modChangeLog.gLoggingActive Then Exit Sub
    
    ' Snapshot the selected range (up to 500 cells for performance)
    If Target.Cells.Count <= 500 Then
        modChangeLog.SnapshotRange Sh, Target
    End If
End Sub

' ============================================================
' WORKBOOK-LEVEL CHANGE: Log every cell edit across all sheets
' ============================================================
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
                                  ByVal Target As Range)
    ' Skip the log sheet
    If Sh.Name = "ChangeLog" Then Exit Sub
    
    ' Skip if actively writing to log (recursion guard)
    If modChangeLog.gLoggingActive Then Exit Sub
    
    ' Skip if logging is suppressed
    If modChangeLog.gSuppressLog Then Exit Sub
    
    ' Temporarily disable events while we process
    Application.EnableEvents = False
    
    On Error GoTo CleanUp
    
    ' Determine the change type
    Dim changeType As String
    If Target.Cells.Count = 1 Then
        If Target.HasFormula Then
            changeType = "Formula"
        ElseIf Target.Value = "" Then
            changeType = "Delete"
        Else
            changeType = "Edit"
        End If
    Else
        changeType = "Paste"
    End If
    
    ' Handle single cell edits
    If Target.Cells.Count = 1 Then
        Dim oldVal As String
        Dim newVal As String
        
        oldVal = modChangeLog.GetOldValue(Sh.Name, Target.Address(True, True))
        
        If Target.HasFormula Then
            newVal = "=" & Target.Formula
        Else
            newVal = CStr(Target.Value)
        End If
        
        Application.EnableEvents = True
        modChangeLog.WriteLogEntry Sh.Name, _
                                    Target.Address(True, True), _
                                    oldVal, _
                                    newVal, _
                                    changeType
        Application.EnableEvents = False
        
    Else
        ' Handle multi-cell paste operations
        ' Write a batch header entry, then individual entries per cell
        Dim pasteComment As String
        pasteComment = "Paste operation: " & Target.Cells.Count & " cells in " & Target.Address
        
        Dim cell As Range
        For Each cell In Target.Cells
            Dim oldCellVal As String
            Dim newCellVal As String
            
            oldCellVal = modChangeLog.GetOldValue(Sh.Name, cell.Address(True, True))
            
            If cell.HasFormula Then
                newCellVal = "=" & cell.Formula
                changeType = "Paste-Formula"
            ElseIf cell.Value = "" Then
                newCellVal = ""
                changeType = "Paste-Delete"
            Else
                newCellVal = CStr(cell.Value)
                changeType = "Paste"
            End If
            
            Application.EnableEvents = True
            modChangeLog.WriteLogEntry Sh.Name, _
                                        cell.Address(True, True), _
                                        oldCellVal, _
                                        newCellVal, _
                                        changeType, _
                                        pasteComment
            Application.EnableEvents = False
        Next cell
    End If

CleanUp:
    Application.EnableEvents = True
    
    If Err.Number <> 0 Then
        Debug.Print "ChangeLog Error in Workbook_SheetChange: " & Err.Description
    End If
End Sub

---

Handling the Snapshot Gap: The Undo Fallback

The SelectionChange snapshot mechanism has a real weakness: if a user edits the currently selected cell without first clicking away (i.e., they just start typing into whatever is already selected when the workbook opens), there's no SelectionChange event to trigger the snapshot. The GetOldValue call returns "[No snapshot]".

We can partially mitigate this with a Workbook_Activate snapshot that captures all visible cells on all sheets when the workbook gains focus:

' In ThisWorkbook module - add this:
Private Sub Workbook_Activate()
    ' Take a lightweight snapshot of the active sheet's used range
    ' when the workbook gains focus - handles the "no prior selection" edge case
    If modChangeLog.gLoggingActive Then Exit Sub
    
    Dim ws As Worksheet
    Set ws = Me.ActiveSheet
    
    If ws.Name = "ChangeLog" Then Exit Sub
    
    ' Only snapshot if the used range is manageable
    If ws.UsedRange.Cells.Count <= 2000 Then
        modChangeLog.SnapshotRange ws, ws.UsedRange
    End If
End Sub

For larger sheets, snapshotting the entire used range on every workbook activate would be too expensive. In those cases, accept that the very first edit after opening without selecting first will show "[No snapshot]" for the old value — which is still a useful record, just incomplete.

---

Protecting the Log from Tampering

An audit log that users can edit or delete is worse than no audit log — it creates a false sense of security. You need to protect the ChangeLog sheet in a way that the VBA system can still write to it, but human users cannot.

The trick is to protect the sheet programmatically but use a password in your VBA code, and use UserInterfaceOnly:=True which allows VBA to write even when protection is on, without VBA needing to unprotect and re-protect on every write:

' ============================================================
' Protect the ChangeLog sheet
' UserInterfaceOnly means VBA can still write; humans cannot
' ============================================================
Sub ProtectChangeLog()
    Dim wsLog As Worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
    On Error GoTo 0
    
    If wsLog Is Nothing Then
        MsgBox "ChangeLog sheet not found.", vbExclamation
        Exit Sub
    End If
    
    ' UserInterfaceOnly := True is the critical parameter
    ' This protection survives file save/reopen ONLY if set on open
    wsLog.Protect Password:="WSD_AuditLog_2024", _
                  UserInterfaceOnly:=True, _
                  DrawingObjects:=True, _
                  Contents:=True, _
                  Scenarios:=True, _
                  AllowFiltering:=True    ' Allow users to filter the log
End Sub

Add ProtectChangeLog to Workbook_Open:

Private Sub Workbook_Open()
    modChangeLog.EnsureDictionaryExists
    ProtectChangeLog   ' Re-apply UserInterfaceOnly protection on every open
    Application.EnableEvents = True
End Sub

---

Handling Sheets You Don't Want Logged

In most real workbooks, you'll have calculation sheets, lookup tables, or helper sheets where logging edits would produce noise. You might want to log changes to "Budget_2024" and "Headcount" but skip "LookupTables" and "TempCalc."

Add a whitelist/blacklist mechanism to WriteLogEntry. We'll use a public constant in the module:

' In modChangeLog - add at the top:

' Sheets to EXCLUDE from logging (comma-separated, case-insensitive)
Private Const EXCLUDED_SHEETS As String = "ChangeLog,LookupTables,TempCalc,Parameters"

' Helper function to check if a sheet should be logged
Public Function ShouldLogSheet(wsName As String) As Boolean
    Dim excluded() As String
    excluded = Split(EXCLUDED_SHEETS, ",")
    Dim i As Integer
    For i = 0 To UBound(excluded)
        If LCase(Trim(excluded(i))) = LCase(Trim(wsName)) Then
            ShouldLogSheet = False
            Exit Function
        End If
    Next i
    ShouldLogSheet = True
End Function

Then modify the check at the top of WriteLogEntry:

' Replace the single line "If wsName = "ChangeLog" Then Exit Sub" with:
If Not modChangeLog.ShouldLogSheet(wsName) Then Exit Sub

And do the same in Workbook_SheetSelectionChange and Workbook_SheetChange:

' Replace: If Sh.Name = "ChangeLog" Then Exit Sub
' With:
If Not modChangeLog.ShouldLogSheet(Sh.Name) Then Exit Sub

---

Adding Log Filtering and Search

The raw log is useful, but what you really need is the ability to ask questions like "show me all changes made by John Smith to the Budget sheet between March 1 and March 15." Let's add a search/filter interface.

' ============================================================
' FilterChangeLog - Apply criteria to the log table
' Call from a button on the ChangeLog sheet or a ribbon command
' ============================================================
Sub FilterChangeLog()
    Dim wsLog As Worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
    On Error GoTo 0
    If wsLog Is Nothing Then Exit Sub
    
    ' Get filter criteria from user
    Dim filterUser As String
    Dim filterSheet As String
    Dim filterStartDate As String
    Dim filterEndDate As String
    
    filterUser = InputBox("Filter by username (leave blank for all):", _
                           "Filter Change Log", "")
    filterSheet = InputBox("Filter by sheet name (leave blank for all):", _
                            "Filter Change Log", "")
    filterStartDate = InputBox("Start date (yyyy-mm-dd, leave blank for all):", _
                                "Filter Change Log", "")
    filterEndDate = InputBox("End date (yyyy-mm-dd, leave blank for all):", _
                              "Filter Change Log", "")
    
    ' Get the table
    Dim tbl As ListObject
    Set tbl = wsLog.ListObjects("tblChangeLog")
    
    ' Clear existing filters
    If tbl.ShowAutoFilter Then
        tbl.AutoFilter.ShowAllData
    End If
    
    ' Apply username filter (column 3)
    If filterUser <> "" Then
        tbl.Range.AutoFilter Field:=3, Criteria1:="*" & filterUser & "*"
    End If
    
    ' Apply sheet filter (column 5)
    If filterSheet <> "" Then
        tbl.Range.AutoFilter Field:=5, Criteria1:="*" & filterSheet & "*"
    End If
    
    ' Apply date range filter (column 2)
    If filterStartDate <> "" And filterEndDate <> "" Then
        tbl.Range.AutoFilter Field:=2, _
                              Criteria1:=">=" & CDate(filterStartDate), _
                              Operator:=xlAnd, _
                              Criteria2:="<=" & CDate(filterEndDate) & " 23:59:59"
    ElseIf filterStartDate <> "" Then
        tbl.Range.AutoFilter Field:=2, Criteria1:=">=" & CDate(filterStartDate)
    ElseIf filterEndDate <> "" Then
        tbl.Range.AutoFilter Field:=2, Criteria1:="<=" & CDate(filterEndDate) & " 23:59:59"
    End If
    
    MsgBox "Filter applied. " & _
           tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count & _
           " records visible.", vbInformation, "Change Log Filter"
End Sub

' ============================================================
' ExportFilteredLog - Export visible log rows to a new workbook
' ============================================================
Sub ExportFilteredLog()
    Dim wsLog As Worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
    On Error GoTo 0
    If wsLog Is Nothing Then Exit Sub
    
    Dim tbl As ListObject
    Set tbl = wsLog.ListObjects("tblChangeLog")
    
    ' Copy visible cells only
    Dim wbExport As Workbook
    Dim wsExport As Worksheet
    
    Set wbExport = Workbooks.Add
    Set wsExport = wbExport.Worksheets(1)
    wsExport.Name = "AuditExport"
    
    ' Copy headers
    wsLog.Range("A1:J1").Copy wsExport.Range("A1")
    
    ' Copy visible data rows
    On Error Resume Next
    Dim visibleData As Range
    Set visibleData = tbl.DataBodyRange.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    If Not visibleData Is Nothing Then
        visibleData.Copy wsExport.Range("A2")
    End If
    
    ' Format the export
    wsExport.Columns.AutoFit
    wsExport.Range("A1:J1").Font.Bold = True
    
    ' Save as xlsx (no macros needed in the export)
    Dim exportPath As String
    exportPath = Environ("USERPROFILE") & "\Desktop\ChangeLog_Export_" & _
                 Format(Now(), "yyyymmdd_HHMMSS") & ".xlsx"
    
    wbExport.SaveAs exportPath, xlOpenXMLWorkbook
    MsgBox "Export saved to: " & exportPath, vbInformation, "Export Complete"
End Sub

---

Performance Optimization for High-Volume Sheets

If your audited workbook has dozens of users making hundreds of edits per hour, naive implementations hit performance walls quickly. Here's where you need to think architecturally.

The Write Bottleneck — Every cell edit currently triggers a synchronous write to the log sheet. Finding the next empty row with End(xlUp) is fine for 1,000 rows but becomes measurably slow at 50,000 rows. Replace the row-finding logic with a cached counter:

' In modChangeLog - add a module-level row counter
Private mNextLogRow As Long

Public Function GetNextLogRowCached() As Long
    If mNextLogRow = 0 Then
        ' Initialize from the actual sheet on first call
        Dim wsLog As Worksheet
        On Error Resume Next
        Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
        On Error GoTo 0
        If wsLog Is Nothing Then
            GetNextLogRowCached = 0
            Exit Function
        End If
        mNextLogRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1
    End If
    mNextLogRow = mNextLogRow + 1
    GetNextLogRowCached = mNextLogRow - 1
End Function

' Call this on Workbook_Open to prime the cache
Public Sub ResetRowCache()
    mNextLogRow = 0
End Sub

Batch Writes for Paste Operations — When a user pastes 200 cells, we're writing 200 individual rows. Each write triggers a separate Application.EnableEvents toggle cycle. Instead, build an array of records and write them in a single operation:

' In modChangeLog - batch write function
Public Sub WriteBatchLogEntries(records() As Variant)
    Dim wsLog As Worksheet
    On Error Resume Next
    Set wsLog = ThisWorkbook.Worksheets("ChangeLog")
    On Error GoTo 0
    If wsLog Is Nothing Then Exit Sub
    
    Dim recordCount As Long
    recordCount = UBound(records, 1) - LBound(records, 1) + 1
    
    If recordCount = 0 Then Exit Sub
    
    Dim startRow As Long
    startRow = wsLog.Cells(wsLog.Rows.Count, "A").End(xlUp).Row + 1
    
    gLoggingActive = True
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    ' Write the entire batch at once using a 2D array
    Dim lastLogID As Long
    If startRow > 2 Then
        lastLogID = wsLog.Cells(startRow - 1, "A").Value
    Else
        lastLogID = 0
    End If
    
    Dim outputArray() As Variant
    ReDim outputArray(1 To recordCount, 1 To 10)
    
    Dim i As Long
    For i = 1 To recordCount
        outputArray(i, 1) = lastLogID + i          ' Log ID
        outputArray(i, 2) = records(i - 1)(0)       ' Timestamp
        outputArray(i, 3) = Environ("USERNAME")      ' Username
        outputArray(i, 4) = ThisWorkbook.Name        ' Workbook
        outputArray(i, 5) = records(i - 1)(1)        ' Sheet
        outputArray(i, 6) = records(i - 1)(2)        ' Cell Address
        outputArray(i, 7) = records(i - 1)(3)        ' Old Value
        outputArray(i, 8) = records(i - 1)(4)        ' New Value
        outputArray(i, 9) = records(i - 1)(5)        ' Change Type
        outputArray(i, 10) = records(i - 1)(6)       ' Comment
    Next i
    
    ' Single range write - dramatically faster than row-by-row
    wsLog.Range(wsLog.Cells(startRow, 1), _
                wsLog.Cells(startRow + recordCount - 1, 10)).Value = outputArray
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    gLoggingActive = False
End Sub

The 65,536 Row Problem — A change log in a heavily-used shared workbook can hit Excel's practical row limits. Consider archiving: once the log exceeds 40,000 rows, automatically export the first 20,000 rows to a dated archive file and trim the log sheet. You can even send the archive to a shared drive or SharePoint location using file operation techniques discussed in Automating Email and File Operations with VBA.

---

Multi-User Shared Workbook Considerations

Enterprise spreadsheets are often shared among multiple simultaneous users. Traditional Excel sharing (via the old Share Workbook feature) and the newer Co-Authoring via OneDrive/SharePoint both create challenges for change logging.

Traditional Shared Workbooks — Excel's legacy sharing model merges changes periodically. The Worksheet_Change event fires for each user's local session, so the Environ("USERNAME") approach correctly identifies each user. However, write conflicts on the ChangeLog sheet itself can cause corruption. Use On Error Resume Next defensively around all log writes, and consider writing each user's changes to a separate hidden "staging" sheet named after their username, with a periodic merge process.

Co-Authoring (OneDrive/SharePoint) — Co-authoring uses a completely different sync model. Events fire correctly for local edits, but the timing of when other users' changes propagate is unpredictable. For true enterprise audit requirements in co-authoring scenarios, the VBA change log should be considered a "best effort" layer, and the authoritative audit trail should live in SharePoint's built-in version history or a connected database.

---

Hands-On Exercise

Build a complete change logging system for a three-sheet financial workbook. Here's the exercise structure:

Setup: Create a new .xlsm workbook with these sheets:

• Revenue_Forecast — 20 rows of monthly revenue projections with editable cells in columns B through M
• Headcount — 15 rows of department headcount data
• LookupTables — Reference tables that should NOT be logged

Part 1: Basic Implementation

1. Insert a standard module named modChangeLog and paste the module-level code from this lesson
2. Add the ThisWorkbook event handlers for Workbook_Open, Workbook_SheetSelectionChange, and Workbook_SheetChange
3. Run InitializeChangeLog to create and format the ChangeLog sheet
4. Run ProtectChangeLog and add it to Workbook_Open
5. Add "LookupTables" to the EXCLUDED_SHEETS constant

Part 2: Test the Logging

1. Navigate to Revenue_Forecast and edit cell B3 — change it from one number to another
2. Navigate to Headcount and edit three cells
3. Copy a range of 5 cells in Revenue_Forecast and paste it elsewhere
4. Press Delete on a cell
5. Check the ChangeLog sheet — verify you see entries with correct timestamps, usernames, old values, and new values

Part 3: Test the Protections

1. Try to directly edit a cell in the ChangeLog sheet — it should be blocked
2. Verify that LookupTables edits do NOT appear in the log
3. Save and reopen the workbook — verify logging still works (the UserInterfaceOnly protection was re-applied by Workbook_Open)

Part 4: Filtering and Export

1. Make a button on the ChangeLog sheet labeled "Filter Log" and assign it the FilterChangeLog macro
2. Make a second button labeled "Export Filtered Log" for ExportFilteredLog
3. Filter by your own username and verify only your edits appear
4. Export the filtered results and open the export file

Challenge extension: Add a Workbook_BeforeClose event that checks whether the ChangeLog has more than 5,000 entries and, if so, prompts the user to export before closing.

---

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that UserInterfaceOnly doesn't survive a save Symptom: After saving and reopening, VBA can't write to the protected ChangeLog, or runtime error 1004 fires inside WriteLogEntry. Fix: Always call ProtectChangeLog in Workbook_Open. Test by saving, closing, reopening, and making an edit.

Mistake 2: Infinite recursion crashing Excel Symptom: Excel hangs or crashes when you make an edit. The stack overflow dialog may appear. Fix: Check that both the gLoggingActive guard and Application.EnableEvents = False are in place before any write to the log, and that Application.EnableEvents = True is restored in a cleanup block. Use On Error GoTo CleanUp with Application.EnableEvents = True inside the CleanUp label.

Mistake 3: Old value always showing "[No snapshot]" Symptom: The Old Value column is always "[No snapshot]" in the log. Fix: Usually caused by Workbook_SheetSelectionChange not firing because events are still disabled from a previous error. Check that Application.EnableEvents is True by typing ?Application.EnableEvents in the Immediate Window. If it's False, run Application.EnableEvents = True to restore it.

Mistake 4: Log captures changes to the ChangeLog sheet itself Symptom: When filtering the ChangeLog (which moves the AutoFilter), you see filter-related events being logged. Fix: The ShouldLogSheet check should catch this, but verify the ChangeLog sheet name in EXCLUDED_SHEETS exactly matches the worksheet's actual .Name property (case matters if your compare mode isn't set to text compare).

Mistake 5: Multi-cell paste logging the same old value for all cells Symptom: All pasted cells show the same old value in the log. Fix: This happens when SnapshotRange was never called for the destination range (the user pasted into unselected cells, e.g., via VBA or a macro). The individual GetOldValue calls will return "[No snapshot]". This is expected behavior — document it in your system design.

Mistake 6: Performance degrading as the log grows Symptom: After 10,000+ rows, there's a noticeable lag after every cell edit. Fix: Implement the row-counter cache (mNextLogRow) to avoid End(xlUp) scanning on every write. Also consider moving from sheet-based logging to an external database as volume grows — a pattern covered in Connecting Excel to External Databases with VBA.

Mistake 7: The log doesn't capture edits made by other macros Symptom: When another macro edits cells, those changes don't appear in the log. Fix: If the other macro sets Application.EnableEvents = False before making changes (a common performance practice), your Worksheet_Change event won't fire. You need to either wrap other macros with explicit log calls, or ensure they restore EnableEvents = True when done. For internally maintained macros, add calls to modChangeLog.WriteLogEntry directly.

---

Extending the System: Advanced Patterns

Once you have the core system running, several extensions are worth considering for production deployments.

Formula Dependency Tracking — When a formula cell changes its output because an upstream input changed, Worksheet_Change does NOT fire (only Worksheet_Calculate does). If you need to track when formula results change value, implement a Worksheet_Calculate handler that compares current formula outputs against a cached snapshot. This is expensive — only do it for specific critical cells, not an entire sheet.

Integration with a Database Backend — For workbooks with 10+ users and compliance requirements, replace the sheet-based log with an ADO connection to a SQL Server or Access database. WriteLogEntry becomes an INSERT statement rather than a cell write. This solves the row-limit problem, enables proper concurrent access, and lets your IT team query the audit trail from outside Excel entirely.

Custom Ribbon Integration — Instead of sheet-based buttons for Filter and Export, build a custom ribbon tab with properly labeled buttons and dropdowns. See Building a Custom Excel Ribbon with VBA and XML for the full implementation pattern. A ribbon button for "View Change Log" that navigates directly to the filtered log view is something end users genuinely appreciate.

Packaging as an Add-In — If you want to deploy this change logging system across multiple workbooks without copying code into each one, package it as an Excel Add-In (.xlam). The Add-In can hook into any open workbook's events using Application-level events. This is an advanced pattern covered in Building Excel Add-Ins with VBA.

---

Summary & Next Steps

You've built a complete, production-quality change logging system. Let's summarize what the system does:

• Captures pre-edit values using SelectionChange snapshots stored in a Scripting.Dictionary with composite sheet+cell keys
• Logs every edit via Workbook_SheetChange, with change type classification (Edit, Delete, Paste, Formula)
• Records full context — timestamp, Windows username, workbook name, sheet name, cell address, old value, and new value — in a structured log sheet
• Protects itself using UserInterfaceOnly sheet protection, re-applied on every Workbook_Open
• Handles performance with cached row counters and array-based batch writes
• Supports filtering and export with AutoFilter-driven queries and one-click Excel export

The system handles the tricky edge cases: recursive event suppression, multi-cell paste operations, formula tracking, sheet exclusion lists, and the UserInterfaceOnly persistence bug.

Where to go from here:

If you want to deepen your understanding of the event architecture powering this system, study Building a Custom VBA Event-Driven Framework — it covers Application-level events that let you hook into events across all open workbooks simultaneously, which is the foundation for an Add-In deployment of this change logger.

For the database backend upgrade, Connecting Excel to External Databases with VBA will walk you through replacing the sheet writes with ADO INSERT statements, giving you a scalable, multi-user audit infrastructure that grows far beyond Excel's row limits.

And if you want to build a formal testing framework to validate that your change log behaves correctly under all the edge cases we discussed, Building a VBA Testing Framework for Excel shows you how to write automated tests that verify old/new value capture, protection behavior, and log entry integrity — so you can deploy with confidence.