Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 21
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 54
Default 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   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by Adrian C[_2_] View Post
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
Hello

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
unexpected error (32809) Student Excel Programming 7 February 6th 07 08:24 PM
Errors 32809 and 57121 :-( Jan[_18_] Excel Programming 0 June 3rd 06 05:56 PM
Run-Time error 57121 Linking to specific cells in pivot table Excel Programming 0 June 23rd 05 07:20 PM
###Error 57121### Jimmy Excel Programming 2 November 10th 04 03:00 PM
error 57121 toine Excel Programming 0 December 29th 03 11:10 PM


All times are GMT +1. The time now is 09:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"