Worksheet change, selection change, before doubleclick stop working

Question

On my sheet I have Worksheet change, selection change and before doubleclick macros. They work like a charm but sometimes they stop working. They don't trigger at all, even when I do a thing that must trigger them.

When they stop to trigger : 1. If i write a macro like

sub test
msgbox "hello" & application.EnableEvents
end sub

and play this macro, it works and show me "hello TRUE".

2. If I add msgbox "hello" at the first line in worksheet change macro, and test it with a thing that must trigger it, nothing happen.

3. If I close the workbook and reopen it, that works again like a charm.

I don't know what can stop these macro. I though that if application.events return "TRUE" then these macros must trigger (and eventually cause errors, but they must trigger). What could I do to test what stop them sometimes ? What could it be ?

Answer 1

Add this code to your worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    ThisWorkbook.Worksheets("Sheet1").Range("A1") = _
        ThisWorkbook.Worksheets("Sheet1").Range("A1") + 1
End Sub

Now enter the number 1 in cell A1. This will fire the worksheet change event, which will add 1 to the value in cell A1, which will fire the worksheet change event, which will add 1 to the value in cell A1.....

Now look at this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    ThisWorkbook.Worksheets("Sheet1").Range("A1") = _
        ThisWorkbook.Worksheets("Sheet1").Range("A1") + 1

    Application.EnableEvents = True

End Sub

Now enter a 1 in cell A1. Event watching is turned off, 1 is added to the value in cell A1 and because event watching is turned off - nothing else happens except it is turned on again after the event would have fired.

Answer 2

I think I just found the bug in my code. I have a "On error goto 0" in my worksheet_change (old code, when I wasn't sure how to handle errors...). I think that an error occured and was not handled properly, with "application.EnableEvents = true" at the end. I hope this is it. I also put "application.EnableEvents = true" in workbook_open and workbook_activate to try to put away the bug I had. Thanks for the tips.