Resolving error 57121

Question

My problem concerns Excel 2007 when controls (label and combo box) exist on a Worksheet.

After Saving/Closing the Workbook error 57121 occurs on reload and the combo box is not populated.

Further investigation shows the worksheet is not accessible e.g.

?  Sheets("Sheet1").name

in the Immediate window also results in error 57121.

If the label and combo box are rmoved everything works OK.

I have tried building the xlsm bit by bit adding modules and Forms and testing as I go, and when the

error occurs I remove the last module that appeared to cause it. But even though it was OK previously it is

no longer. So adding a module caused the error, but it stayed after that module was removed.

A solution is very much needed. Thank you for any assistance.

Answer 1

Thanks for the response.

Yes, the controls are on a Worksheet.  The Forms and ,Modules were being added back one by one back into the Project while rebuilding the workbook and checking for the error.

It does not work with just one label.

The combo boxes are populated from Workbook_Open using .AddItem method.

The workbook is huge and it's hard to select which  part of the code to post as  the error doesn't seem to  be the code. For example, the (same) code works in Excel 2003 enviroment,, but not 2007.

The error always occurs in Workbook Open.

It is like the problem is Excel. To recreate I start with a blank workbook then add the controls.

I save, close Excel, reload and add a module, repeating these steps until the error occurs.

The modules are not called, just added to the project.

My Workbook_Open consists of

Private Sub Workbook_Open()

 #If VBA6 Then

    #Else

        MsgBox "Macros in this Spreadsheet requires a later version of Excel"

        Exit Sub

 #End If

Sheets("Sheet1").Select

The last line is highlighted when the error happens. Otherwise execution continues correctly.

I've also seen the link from Andreas and applied all the suggestions there . The result was a Workbook

which did error worked fine - BUT only once. When saved and reloaded, the same error returned.

Answer 2

Also see the following link.

http://answers.microsoft.com/en-us/office/forum/office_2007-customize/application-error-57121-on-worksheetssheet1/1cc1c254-42ba-4bb9-b443-87285b8d8202

Answer 3

You mention that the Label and ComboBox exist on a Worksheet and then go on to mention adding Forms and Modules. Just to confirm, am I correct in assuming that these controls are on a Worksheet and not on a Userform?

Does it work with just the ComboBox removed and leave the label in situ? If not, I would suggest you might have a corrupted workbook and I would start another one and copy the required data to it and re-create the controls.

I am thinking that maybe one of the ComboBox properties is not correctly set. How are you applying the ComboBox selection list? Is it applied with VBA code when the workbook is opened or have you applied a range to the ListFillRange property.

Your comment: "adding a module caused the error, but it stayed after that module was removed." Can you post the code that is in this module; particularly anything related to the combobox because I am still thinking that invalid info is assigned to one of its properties.