0

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

FinancialRadDeveloper
  • 984
  • 4
  • 13
  • 28

2 Answers2

4

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.

CaBieberach
  • 1,748
  • 2
  • 17
  • 26
  • 1
    As well as your code, bear in mind that other people's code can execute the same statement (if they're running multiple addins). In my experience of this, it was never my code that was turning off events and then crashing immediately. – Chris Rae Feb 15 '11 at 20:46
  • +1 on that @Chris R. Particularly to be careful when the statement is present in a "OnCalculation" event. When your workbook calculates/recalculates Excel will also trigger any calculation procedure on every workbook open. – CaBieberach Feb 15 '11 at 22:27
  • Thanks for the answers. There was a method that was indeed setting .EnableEvents = False. There was an option to fail silently and both of the error handling sections did not set it back to True, which has now been rectified. Cheers – FinancialRadDeveloper Feb 17 '11 at 09:40
0

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 :)

Mahmoud Sayed
  • 151
  • 2
  • 10