Excel behaves like EnableEvents set False when it is still set True

[Ronnie101]

I work in a corporate environment using Excel 2003 and 2007. We have developed many spreadsheets that use events to operate correctly - for example, when a dropdown's value is changed then SQL queries are run to repopulate a set of ranges with the correct lookup values.

However, after an unpredictable time in use, the Excel session stops responding to events as though Application.EnableEvents were set to False, even though Application.EnableEvents is still set True.

It's an issue with Excel itself, since if you close and reopen the spreadsheet in the same Excel session the same fault continues to show, but if you close Excel completely and reopen the sheet in a new Excel session the fault has gone (until it re-occurs some random time later).

It seems to happen to all of us in the department. We first noticed the issue about 18 months ago so it may be some issue with a certain Service Pack or update from then?

Our support staff have no idea how to fix it. Has anyone else here got any suggestions?

Thanks!

 

[Jerry Sullivan]

Hi Ronnie and Welcome to the Board,

It could help to rule-in or out a few possibilities....

1. It's possible that VBA execution stopped at an unhandled error which would preclude you from running other code until VBA is reset. Test by trying to run a standard macro from a button or the Alt-F8 menu after your Events have stopped responding.

2. Similarly, VBA could be running and not stopped (ie, caught in a loop). When in the VB Editor, check to see if "[running]" appears in the VB Editor Title after the filename.

3. Is it possible that the events are triggering, but the event code procedure is being exited before it does the steps you are expecting? If you're not sure, place a breakpoint or Msgbox statement at the beginning of the procedure.

4. Have you tried testing other events to see if the problem isn't specific to the 1-2 events you have associated with your procedures?

5. Lastly, ensure you don't have code for the same events in an Add-In or Class Module that are unintentionally conflicting with the code you want to execute.