CODEWITHSUNDEEP
vbaexcelevents
Spurious
Spurious

Reputation: 1995

Worksheet change event for every sheet in the workbook

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.

Upvotes: 5

Views: 12856

Answers (2)

R3uK
R3uK

Reputation: 14537

You should use :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

It works like the Sheet_Change event except that instead of Me., you'll use Sh. to reference the sheet that triggered the event.

Upvotes: 3

user4039065
user4039065

Reputation:

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.

Upvotes: 5

Related Questions