[Excel VBA] How to Log Cell Changes Across All Open Workbooks (Application-Level Events)

Introduction

If you can automatically record “who changed what, when, and in which file,” you can significantly improve the reliability and auditability of your shared data.

While the standard Worksheet_Change event can only monitor a single sheet, VBA’s Application-level events allow you to detect changes in all open workbooks and sheets and record them in a single log sheet.

In this article, I will explain the full VBA code and key implementation points to achieve this.

Important: Where to Write the Code

This code monitors the behavior of the entire Excel application. Therefore, you must write all the code in the ThisWorkbook module. It will not work correctly if written in a Standard Module or a Sheet Module.

1. Open the Visual Basic Editor (Alt + F11).
2. Double-click ThisWorkbook in the Project Explorer.
3. Paste all the code below into the code window.

Preparation: The Log Sheet

This macro writes the change history to a specific sheet. Create a sheet named “LogSheet” in the workbook where you write this macro, and set up the headers in the first row as follows:

Column A	Column B	Column C	Column D	Column E
Date/Time	Workbook Name	Sheet Name	Cell Address	New Value

Complete VBA Code

'==================================================
' Write all this code in the ThisWorkbook module
'==================================================

'--- 1. Declare variable for event detection ---
' The WithEvents keyword makes this a special event variable
Private WithEvents AppEvents As Excel.Application

'--- 2. Start monitoring when workbook opens ---
Private Sub Workbook_Open()
    ' Assign Application object to the event variable
    Set AppEvents = Application
End Sub

'--- 3. Stop monitoring before closing ---
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ' Release reference to the event variable
    Set AppEvents = Nothing
End Sub


'--- 4. This event runs the moment a cell is changed ---
Private Sub AppEvents_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    ' Declare variables
    Dim logSheet As Worksheet
    Dim nextRow As Long

    ' Specify the sheet to record logs
    On Error Resume Next ' Avoid errors if LogSheet doesn't exist
    Set logSheet = ThisWorkbook.Worksheets("LogSheet")
    If logSheet Is Nothing Then Exit Sub
    On Error GoTo 0

    ' [Loop Prevention 1] Do not record if change happened in the LogSheet itself
    If Sh.Name = logSheet.Name Then Exit Sub
    
    ' Get the next available row in the log sheet
    nextRow = logSheet.Cells(logSheet.Rows.Count, "A").End(xlUp).Row + 1
    
    ' [Loop Prevention 2] Temporarily turn off events during writing
    Application.EnableEvents = False

    ' Write change history to LogSheet
    With logSheet.Cells(nextRow, "A")
        .Value = Now()                             ' Date and Time
        .Offset(0, 1).Value = Sh.Parent.Name       ' Workbook Name
        .Offset(0, 2).Value = Sh.Name              ' Sheet Name
        .Offset(0, 3).Value = Target.Address(False, False) ' Cell Address
        .Offset(0, 4).Value = Target.Value2        ' New Value
    End With
    
    ' Re-enable events
    Application.EnableEvents = True
    
End Sub

Code Explanation1. Declaring with WithEvents

Private WithEvents AppEvents As Excel.Application

By declaring a variable with the WithEvents keyword, that variable can “intercept” various events (such as cell changes or opening workbooks) occurring within the Excel application.

2. Starting and Stopping Monitoring

Inside Workbook_Open, we execute Set AppEvents = Application. This tells the variable to start watching the entire Excel application as long as this workbook is open. Conversely, we use Set AppEvents = Nothing in Workbook_BeforeClose to stop monitoring.

3. Catching the Change Event (AppEvents_SheetChange)

Private Sub AppEvents_SheetChange(...)

This is the main procedure that runs when a cell is changed. The naming rule is strictly “VariableName” + underscore + “EventName”.

• Sh As Object: Represents the worksheet where the change occurred.
• Target As Range: Represents the cell range that was changed.

4. Preventing Infinite Loops

The most critical part of this code is preventing infinite loops.

1. Skip LogSheet: Writing to the log is also a “cell change.” We use If Sh.Name = logSheet.Name Then Exit Sub to ensure we don’t log changes made to the log sheet itself.
2. Disable Events: To be absolutely safe, we wrap the writing process with Application.EnableEvents = False / True. This ensures that the act of writing the log does not trigger the event listener again.

Summary

By using Application-level events, you can create advanced tools that monitor operations across Excel without needing to place macros in every single workbook.

1. Declare a variable with WithEvents in the ThisWorkbook module.
2. Set Application to that variable in Workbook_Open.
3. Write the logic inside the VariableName_EventName procedure.
4. Be extremely careful to prevent infinite loops (event chains).

Although this is an advanced technique, it is a powerful solution for creating audit logs or enforcing controls across all open files