Protecting VBA project causes automation error : The object invoked has disconnected from it's clients

[dgardineer]

My environment is XP running office 2007. I am developing an excel application using VBA forms. everything works fine, no errors, until I protect the VBA project. When I protect it, I close and save the workbook and then re-open it. Many times it works the first time I open it and fails on the second and other times it fails on the first. I have done some research on it and found many posts making reference to using unqualified references to workbooks, sheets etc. in the code will produce the automation error. When I get the error I am loading my input form, populating the dropdown boxes and then setting values to the controls. When the error happens the dialog box shows and it doesn't give me the option to debug the code because the VBA project is protected. When it's not protected I don't get the errors so it has proved difficult to debug. The workbook has an autoOpen module that runs when the workbook starts. In this module I setup the excel environment. I set application.displayfullscreen = true, formula bars, displaystatusbar etc. so that the only thing the user sees is the input form. So the order of events is AutoOpen which sets the environment and then it shows the input form. In the input form I set some variables, populate a number of combo boxes and then I set the values of the controls. Now because the VBA project is protected I added message boxes to the error routines to try and catch the error to determine which statement was causing the error. I used MX-tools to set the line numbers and used ERL to see what line was causing the error. So after setting this up, I ran it the first time and did not encounter the error. I opened it a second time and the error routine fired and the message box showed me that the routine in the AutoOpen module with a line number was where the error was. So I went to the AutoOpen routine and commented out the line indicated on the messageBox. I ran it again and again the error routine fired and gave me another line number for a different statement. I commented out that line and did it a third time and it pointed me to an endif statement. It was clear that the error was not in the autopen routine. I then commented out the routine in the input form that sets the controls. This seamed to work. I didn’t get the error. But I have a button on the form that manually populates the controls and I pressed it and got the “Excel has encountered an error and needs to close” error. It is clear to me that the issue is in the routine that populates the controls in the input form but only when the workbook is locked. If I unlock the workbook and run the tests again. everything runs. The reason that I’m concerned is because this is a client deliverable and I can’t send out the file unless it is password protected. Does anyone have any ideas on how to fix this? I have been trying to fix this for the last 2 days and I can’t get past it. Any help or suggestions would be greatly appreciated.