Bypass the Workbook_SheetChange event

Question

I don't know why, and this is quite strange, but my code is looping. Indeed, in a first sheet (ws1), i'm doing this on a Worksheet_Change event :

Application.EnableEvents = False
ws2.Range(Col_Letter(target.Column) & LastLine & ":" & Col_Letter(target.Column) & LastLine ).PasteSpecial Paste:=xlPasteValues
Application.EnableEvents = True

Moreover, I have, on the ThisWorkbook "class", a Workbook_SheetChange event.

On the code above, I'm modifying a Cell. The problem is I don't know why it's calling the Workbook_SheetChange event, because I've already disabled the Events on my first worksheet.

Because of that, it's looping, because on my Workbook_SheetChange event i'm modifying the Worksheet1. And then, the Worksheet1 is modifying the second one, .... infinite loop.

I don't know why Application.EnableEvents = False isn't working (indeed, if I do Debug.Print(Application.EnableEvents) on the Workbook_SheetChange event, it's saying "True" which is quite upset.)

Thanks, Clement

Answer 1

You can work this problem without necessarily disabling events if you use a global flag to indicate what kind of processing is happening.

It's not entirely clear from your design description why you need to handle both Worksheet_Change events and Workbook_SheetChange events. But a good understanding of when each event will fire certainly helps. In your case, if you change a cell on Sheet1 you'll get a Worksheet_Change event for Sheet1, then get a Workbook_SheetChange event. This will happen anytime you change ANY sheet.

The example below illustrates how you can handle the processing to keep it all straight and not step on changes that you are making via VBA and changes made manually. There's multiple parts to the example, so follow along at home...

First, we need to establish two Public things: a workbook-global flag and a workbook-global subroutine to perform your processing. So in a VBA code module (Module1), we have the following code:

'------ Module1 --------
Option Explicit

Public MyCustomMacroInProgress As Boolean

Public Sub DistributeChangeToSheets()
    Debug.Print "Entering DistributeChangeToSheets... "
    MyCustomMacroInProgress = True
    Debug.Print "=== setting MyCustomMacroInProgress = " & MyCustomMacroInProgress

    '--- loop to fire change events in the other worksheets for testing
    Dim ws As Variant
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Sheet1" Then
            ws.Range("A1") = 1
        End If
    Next ws

    MyCustomMacroInProgress = False
    Debug.Print "=== setting MyCustomMacroInProgress = " & MyCustomMacroInProgress
    Debug.Print "Leaving DistributeChangeToSheets"
End Sub

(Please note that using Option Explicit everywhere vastly helps your code.)

By creating a public global flag (MyCustomMacroInProgress), you now have the ability to check this flag in any module and from any class and any worksheet. By creating the Sub DistributeChangeToSheets in the module and making it public, you can essentially apply this type of processing from any originating sheet you might want. My example only applies the change from Sheet1.

So now to the main change catcher - Sheet1. In your code for Sheet1, I have

Option Explicit

'--- in Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "In " & Target.Parent.Name;
    Debug.Print ": Worksheet_Change event fired ";
    Debug.Print " - changed cell " & Target.Address

    DistributeChangeToSheets
End Sub

I'm printing Debug statements everywhere so it's easier to follow where the processing is going. You can see that in Sheet1, we're printing where we are and then calling the sub to DistributeChangesToSheets (from Module1).

My example only has three worksheets, so the code in the other sheet modules is simple:

Option Explicit

'--- in Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "In " & Target.Parent.Name;
    Debug.Print ": Worksheet_Change event fired ";

    If Not MyCustomMacroInProgress Then
        Debug.Print " - process change event normally"
    Else
        Debug.Print " - skip normal change event processing"
    End If
End Sub

And

Option Explicit

'--- in Sheet3
Private Sub Worksheet_Change(ByVal Target As Range)
    Debug.Print "In " & Target.Parent.Name;
    Debug.Print ": Worksheet_Change event fired ";

    If Not MyCustomMacroInProgress Then
        Debug.Print " - process change event normally"
    Else
        Debug.Print " - skip normal change event processing"
    End If
End Sub

Putting this all together in the example now, type a value into any cell on Sheet1. The immediate window now shows the following output:

In Sheet1: Worksheet_Change event fired  - changed cell $B$2
Entering DistributeChangeToSheets... 
=== setting MyCustomMacroInProgress = True
In Sheet2: Worksheet_Change event fired  - skip normal change event processing
In Sheet2: Workbook_SheetChange event fired  - skip normal change event processing
In Sheet3: Worksheet_Change event fired  - skip normal change event processing
In Sheet3: Workbook_SheetChange event fired  - skip normal change event processing
=== setting MyCustomMacroInProgress = False
Leaving DistributeChangeToSheets
In Sheet1: Workbook_SheetChange event fired  - process change event normally

From the output, you can see the sheet and workbook events all follow the pattern of events -- so you get two events when a worksheet is changed (Worksheet_Change and Workbook_SheetChange). By setting your global flag on Sheet1 (from inside DistributeChangeToSheets), by using an If statement you can inhibit change processing on all the other sheets during this kind of processing.

It might involve a bit of re-coding on your part, but handling your events in an ordered manner can make the flow of the processing a bit clearer.