Solved: Automation Error 440

[CaptRon]

Everything was going just fine until I had the great idea to try and add a userform with a listbox and a combobox to regulate user access to certain features.

The user form contain a combobox that uses a drop arrow to reveal a short list of authorized users and a listbox beneath it where the user enters the password that corresponds to their user name. The user names, user passwords, and the password to protect the sheets and workbook all reside on a hidden sheet assessible by the program administrator.

I get an error message when I close the userform that reads:

Runtime error '-2147417848 (800100108)'

Automation error
The object invoked has disconnected from its clients.

From this point I can close the workbook, but Excel 2003 (and Excel 2000) won't close. I have to force a close through the task manager.

It seems I might have eliminated the problem by NOT loading the userform via the macro assigned to that task, but only showing it - frmDE.Show. My cancel button on the userform does unload the form and I don't get the error.

Show and Hide, or Show and Unload, I'm OK. But Load - Unload, I get application lock up. And I cannot enter anything into any of the cells.

Any ideas about what causes this error and how I ought to best avoid it? The userform seems to be working OK right now, but I'm not comfortable about it.

Thanks for any information.

Ron

[mdmackillop]

Hi Ron,
Can you post a sample book. Why a listbox for entering the password. Should this not be a textbox?

[CaptRon]

Good Morning,

At least it's early morning here. This file is over 800K so I posted it in my Keep and Share site.

http://www.keepandshare.com/doc/view.php?u=512758

As they say in the local vernacular, my tired is hanging out so I'll be heading for the ol' bunkhouse, but I'll check back after breakfast.

Hope you can make heads or tails out of this. Thanks for taking the time to look at it.

By the way, it is a textbox, not a listbox. My goof up. (Edit: password, if needed is zxzx)

Ron

[mdmackillop]

Hi Ron,
I'm not getting access to the file. You should be able to post it, as the size limit increased recently, or zip it if it's still too large

[CaptRon]

OK, here it is. Thanks for taking a look. The passwords are uniformly set as zxzx for now.

Ron

[mdmackillop]

Hi Ron
I'm not getting that error, but in stepping through the closing code:
Remove toolbars is looping
A lot of event codes are being triggered
There are a couple of Save prompts
When I reopen, the book is not protected and all sheets are visible..

[CaptRon]

Well, that's not good! I'm going to have to read up about stepping through code and debugging, etc. and get on this like a duck on a June bug.

I can make Excel lock up by changing the code from frmDE.Show to Load frmDE in the initiating ShowDE macro. Otherwise, if I just show and hide this userform, I don't have a problem. The issue could be related to all that unnecessary stuff going on during close, although the problem does not occur at close, only when trying to unload the userform frmDE after loading it. No other userforms create this behavior.

Let me take a hand at cleaning this thing up and I'll hollar back at you for a second peek. I appreciate your assistance more than you know. I drive along all ignorant and happy until I get stuck on something, then I need some heavy duty help to get off high-center.

Thanks,

Ron

[CaptRon]

This dugging business is a bit tedious. However, I did discover and resolve several issues in the process.

In stepping through the code, I noticed that once the OK button is clicked on my userform, the code progresses until, at last, it executes the cancel button which unloads the userform. I didn't ask it to do that, it just finishes that way on its own.

That's OK with me, but I had to change the code in a preceeding macro from Unload frmDE to frmDE.Hide. Apparently, Excel doesn't like trying to unload what's already been unloaded. With this change, I've had no further problems as described in my initial post.

Thanks for your help.

Ron