Excel events ignored / no longer being caught i.e. Worksheet_Change not being entered on change

Question

I am working on a complex spreadsheet based solution. Occasionally a user will experience a problem where the events in the sheet seem to no longer be caught by Excel.

I cannot replicate this behaviour at present, but I have visted the PC in question and I can confirm that despite putting a breakpoint on the following none of them are being called :

Worksheet_Activate()
Worksheet_Deactivate()
Worksheet_Change(ByVal Target As Range)
Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

This will of course be solved by the old classic 'have you tired turning it ( Excel ) on and off again' but I was wondering how this can happen, and how it may be prevented?

I even put Stop / MsgBox "blah" into the methods to see if it was just the breakpoint in VBE but I didn't get anything for that.

So, anyone know why excel is no londer raising the events?

Cheers.

FRD

Answer 1

Check your codes for:

Application.EnableEvents=False

If you turned it off, Excel wont turn it on again when your prodecure ends so be sure to set this parameter to TRUE at the end of your procedure.

If you set it to False at the begining of your procedure and you stop your procedure before it is set to True again, then it stays off.

If your code crashes before reaching the Application.EnableEvents=True line, then it will remain off also.

Answer 2

I've had this issue once on a sheet that doesn't contain any formulas... and that was the reason why Excel was not executing the Calculate Event!

Note.. the Calculate Event I needed to fire some code if a data-table was filtered.. so what I ended up doing was adding a small Sum() that pointed at a column in my table in a hidden cell and funny enough that had the calculate event to fire each time the sheet was filtered :)