Run-time error 57121 & 32809, Excel 2003 SP2

[Adrian C]

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 ... :-(

 

[joeeng]

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.