It's a long story. If anyone has some suggestions, I'd be very grateful.
My workbook works fine in Excel 2003 and in Excel 2007 compatibility mode. The problems start showing up when the file is converted to the 2007 format.
When the original xls file is converted to xlsm format and then reopened using the Trust Center option "run all macros without notification", I get the error:
run-time error 57121 (Application-defined or object-defined error)which then proceeds to crash Excel entirely if I select the "Debug" option.
However, if I press the "End" option when the error appears, there doesn't appear to be any further problems. Also, the error never appears if the Trust Center is set to "disable all macros with notification" and I manually enable the macros when opening the file.
This led me to suspect event code is being executed before the entire application is fully compiled, and I found the following problem.
When I edit the VBA code so that the editor flags it as needing to be compiled (simply adding a comment works), and I close the file without compiling, then the above problem goes away. I also noticed that compiled VBA doesn't stay compiled when re-opening the xlsm file.
ISSUE (and solution?) #1: Do not save the file when fully compiled. Instead, make sure it needs compiling before being saved.
Unfortunately, after fixing the above problem, a more serious problem became forefront when the file is opened:
"Compile Error: Circular dependencies between modules".
or "Compile Error: Member or data not found".
As with the original problem, this happens only when the Trust Center is set to "run all macros without notification". This time I could tell where in the compile sequence it failed, as it brought up VBE automatically, pointing to the problem subroutine in break mode. Turns out it couldn't find one of the worksheet objects. To fix, I had to replace code such as
with the code
In other words, the worksheet code-named InputSheet was not yet properly visible to the compiler. It was visible if I stopped execution and recompiled, but by then, the initialization routines had already aborted. By using the indexed Worksheets method, it resolves objects at run-time and I was able to avoid having the compiler having to know about the object, but this is obviously a serious flaw. Also if a user changes the name of the worksheet tab, this workaround will fail, which is why I prefer using the worksheet's code-name.
ActiveX and Worksheet Event interactions
Although it seemed that things were getting better, turns out I was mistaken. ActiveX and worksheet events were plaguing the whole system. The events would appear to execute OK but when the file was saved and then re-opened, Excel would immediately crash. These are normal ActiveX controls such as Microsoft's ComboBox.
ISSUE 2: Event triggered code can damage the file because an object isn't yet initialized. This problem exists on ActiveX events as well as worksheet events. The damage occurs before (during?) the filesave, but Excel crashes immediately after the workbook is reopened with "run all macros without notification".
I ran into variants of issue 2 when opening the xslm file using the file converter for Excel 2003. (Remember that the original file fully functions in Excel 2003). When converting the xlsm file back to Excel 2003, I get the following error:
Uninitialized ActiveX controls will not be opened in this version of Excel.
Can't exit design mode because Control 'OK_Button' can not be created.
The worksheet code-name InputSheet that triggered the "missing object" during compile has ActiveX controls. So I thought maybe I simply need to rebuild the controls from scratch or delete the event code for the ActiveX controls. Neither option worked. Things would work the first time the file was opened, but after saving and reopening it, Excel would crash immediately. No luck trying to find out what aspect didn't work.
Sorry about such a long bug report, but I thought the more info, the better the chances something I observed might have been seen before. If this can be fixed, it would be wonderful. In the meantime, I can continue to operate using the .xls compatibility mode.
Also posted on MSDN