Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 57121 & 32809, Excel 2003 SP2
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run-time error 57121 & 32809, Excel 2003 SP2
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. "Adrian C" wrote: 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 |
#3
|
|||
|
|||
Quote:
I managed to get past this bug by saving my (Excel 2010) workbook as an Excel Binary (.xlsb) workbook. After that, the VBA macro worked fine. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unexpected error (32809) | Excel Programming | |||
Errors 32809 and 57121 :-( | Excel Programming | |||
Run-Time error 57121 | Excel Programming | |||
###Error 57121### | Excel Programming | |||
error 57121 | Excel Programming |