Run-time error 57121 & 32809, Excel 2003 SP2

Discussion in 'Microsoft Excel Programming' started by Adrian C, Jul 11, 2007.

  1. Adrian C

    Adrian C Guest

    Hi,

    As far as I know, Microsoft has no answer for the following issue on the
    PC platform. This fault corrupts the VBA code module - the only way to
    rescue the workbook is to use the appspro VBA code cleaner :-(

    SYMPTOMS:
    Workbook_Open() crashes with either of the following errors.

    - Run-time error '57121':
    Can't exit design mode because Control '<Control>' can not be created.

    - Run-time error '32809':
    Application-defined or object-defined error

    TO REPRODUCE:

    1. Open Excel 2003
    2. From the 'Control Toolbox' toolbar, place a command button on the
    first worksheet, 'Sheet1'
    3. Open the VB Editor (Alt-F11)
    4. Insert a UserForm
    5. From the Toolbox, draw a TextBox control on the UserForm.
    6. Go to the properties window for the TextBox control, and set the
    ControlSource property to Sheet1!A1
    7. Open the 'ThisWorkbook' code module, and type the following

    Option Explicit
    Private Sub Workbook_Open()
    MsgBox Sheet1.Name
    End Sub

    8. Make sure that the VB Editor is showing the design view of the
    UserForm, then do a 'Debug->Compile VBAProject'
    9. Save the workbook to a file & close Excel.

    10. Finally... Open the saved workbook. Excel will promptly crash with
    the error's mentioned above in SYMPTOMS - the MsgBox never runs.

    -&-

    Interestingly, if at step#8 the 'Debug->Compile' is done WITHOUT the
    design view display of the UserForm, then the saved workbook code module
    is not corrupted. Also if the ControlSource property (step#6) is not
    set, or the command button (step#2) is removed then the issue does not
    appear at all.

    WORKAROUND

    As you can imagine having the above working properly is a basic
    necessity for developing Excel worksheet applications with helper
    UserForms for data entry!

    I found that setting TextBox's ControlSource property late using VBA
    rather than specifying it in the design properties window works around
    the issue and ensures that the saved workbook is not corrupt.

    Private Sub UserForm_Initialize()
    TextBox1.ControlSource = "Sheet1!A1"
    End Sub

    Private Sub UserForm_Terminate()
    TextBox1.ControlSource = ""
    End Sub

    So, there seems to be a big problem with ActiveX controls in Excel. I've
    seen this mentioned before in this newsgroup earlier this year (ping
    joeeng!) but no fixes from MS.

    I have less hair this evening than the one before ... :-(
     
    Adrian C, Jul 11, 2007
    #1
    1. Advertisements

  2. Adrian C

    Guest Guest

    The best workaround that I have found is, believe it or not, to password
    protect (with a simple password to make things easy for editing) the vba
    project. I don't know why this works, but it works every time for me. I am
    sure that this is one of those undocumented bugs, but Microsoft seems to be
    ignoring it because I have found evidence on the web that it has existed
    since Excel 97. Hope this helps.
     
    Guest, Oct 4, 2007
    #2
    1. Advertisements

  3. Adrian C

    JoFo

    Joined:
    Oct 13, 2008
    Likes Received:
    0
    Results!

    Adrian C,

    Thank you very much for your post! I have been plagued by these error messages in a very large and important project for a year now and have been at wits end to find a fix. Your tip about removing the command button is what worked for me. I had a couple of buttons on "Sheet1" that I have now eliminated and this fixed the problem on a formerly buggy tool.

    Re the followup suggestion to lock the VBA project. This did not work for me as the VBA project on the offending workbook has been locked for awhile and the problem has persisted. Perhaps there is some sequence to the locking that makes the difference?

    Again, many thanks! I don't know how you figured that out but I am grateful!

    JoFo
     
    JoFo, Oct 13, 2008
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.