vba - Worksheet change event for every sheet in the workbook

Question

Is there a way to globally install a change event that triggers on every single sheet?

I have a workbook with 52 sheets and I want to have the same event handler triggered. A central module with the code would help to manage the Trigger code but I don't want to add the code to every single sheet.

Is there a way to do it? Implementing it in the workbook module didn't Trigger anything.

Answer 1

Use the Workbook_SheetChange event macro. The Sh is the Worksheet Object being passed in and the Target is the cell or cells that have received the change. A simple With ... End With statement should be enough to localize teh worksheet receiving the change.

A simple timestamp event might look like this.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        If Not Intersect(Target, .Columns(1)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            Dim rng As Range
            For Each rng In Intersect(Target, .Columns(1))
                rng.Offset(0, 4) = Now
            Next rng
        End If
    End With
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Any change made to a cell in column A of any worksheet will result in the current datetime being put into column E of the same worksheet and row. A newly created worksheet will immediately be affected. You can parse the worksheets affected by their Worksheet .CodeName property or Worksheet .Name property. The Worksheet.Index property is not recommended unless you lock the workbook structure so that the worksheets cannot be reordered.