Automation Error: "The object invoked has disconnected from its clients"

Hi All,

Could anyone shed some light on the following??

I have written some VBA code which controls a spreadsheet as follows:

As soon as the file is opened a userform appears, which is the size of the screen. This has several controls which are the only user interface. You never get to actually see excel or the worksheets. There are several different control buttons which call up different userforms to accept input from the user and this data is stored in different worksheets. Some of the buttons also pool the data in several different arrangements on a worksheet and print the sheet out.

The issue I am having is as follows:

One of the buttons calls a userform (called calender) which is the main user interface. When the calender form is called it gathers some data from a worksheet using the activate event. From user input several different variables are calculated and updated on the calender form using the afterupdate and change events. There is a save button which then updates these values to the correct worksheet for storage and the calender form closes back to the main menu form. On the main menu form there are 2 buttons for 'close and save' and 'close and don't save' which do exactly that to the workbook. Once one of these buttons has been pressed, the relevant procedure is carried out and the workbook closes.

This is all working fantastically well and I thought I had completed the project but then when I was road testing it I found a bug which I can't get to the bottom of.........................

When you call the main menu form straight from the VBA design window everything works fine all day long. When you open the file (which calls the main menu) everything works fine first time around. However if you choose the 'close and save' and the workbook closes, and then you close excel, then re-open the file, the main menu loads fine, but when you click the calender form (which looks up data from a worksheet) half of the values, update and then the whole thing freezes in a strange manner. The other values never update, all of the other controls cease to work although you can click the buttons and change the values - just nothing happens with them at all. Its as though the original macro is in some sort of eternal loop.

I have tried the following to de-bug. Originally the value updated on the calender form using the activate event. I tried changing this to initialize event and re ran. Same thing. First time round fine - then save/close - close excel - re-open and error - only this time it never actually loads the calender form, I get "Runtime Error '-2147417848 (80010108) Automation Error. The object invoked has disconnected from its clients" - and back to nothing working.

I have tried more explicitly naming where the variables update and are sourced from to ensure that the correct control has the focus but no joy. It seems strange to me that it always works first time, and crashes second time???
Even more strange is that if you perform the save/close but DO NOT close excel and re-open the file it works absolutely fine again. Its as though when you close the workbook and ALSO close excel something happens that won't let some of the variables update. When I take these variables out of the code the whole thing works absolutely fine no matter what you do.

Has anyone got a clue? Why does it work from design mode flawlessly and from the first runnning flawlessly, and even if you close and re-open without closing excel, but faults when you exit the workbook and exit excel then re-open.
(ps I have also tried resetting the variables to 0 on exit but still no joy)

The variables that fault are very simple along the lines of textbox1.value + textbox2.value.

Please help............................................................

Welcome to the forum, cartman.

Had you included the actual error message in your title, it would assist us in creating a searchable database of threads. Please take a few minutes to read the forum rules before posting again.

Perhaps http://support.microsoft.com/default...;en-us;Q319832 will help.