Consulting

Results 1 to 8 of 8

Thread: Solved: Automation Error 440

  1. #1
    VBAX Contributor CaptRon's Avatar
    Joined
    Mar 2007
    Location
    Austin, Texas
    Posts
    141
    Location

    Solved: Automation Error 440

    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
    Before you criticize someone, you should walk a mile in their shoes. That way, when you criticize them, you're a mile away and you have their shoes.

  2. #2
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Ron,
    Can you post a sample book. Why a listbox for entering the password. Should this not be a textbox?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  3. #3
    VBAX Contributor CaptRon's Avatar
    Joined
    Mar 2007
    Location
    Austin, Texas
    Posts
    141
    Location
    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
    Last edited by CaptRon; 04-15-2008 at 10:18 AM.
    Before you criticize someone, you should walk a mile in their shoes. That way, when you criticize them, you're a mile away and you have their shoes.

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  5. #5
    VBAX Contributor CaptRon's Avatar
    Joined
    Mar 2007
    Location
    Austin, Texas
    Posts
    141
    Location
    OK, here it is. Thanks for taking a look. The passwords are uniformly set as zxzx for now.

    Ron
    Before you criticize someone, you should walk a mile in their shoes. That way, when you criticize them, you're a mile away and you have their shoes.

  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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..
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  7. #7
    VBAX Contributor CaptRon's Avatar
    Joined
    Mar 2007
    Location
    Austin, Texas
    Posts
    141
    Location
    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
    Before you criticize someone, you should walk a mile in their shoes. That way, when you criticize them, you're a mile away and you have their shoes.

  8. #8
    VBAX Contributor CaptRon's Avatar
    Joined
    Mar 2007
    Location
    Austin, Texas
    Posts
    141
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •