Runtime Error 57121 When opening workook after compiling with Excel 2010

Question

Whenever I Compile my VBA, and reopen the file, I get the RTE 57121 Application defined or object-defined error on the Sheets("MainMenu").Select line of the code. If I select End, the file opens, and the message does not appear the next time the file opens.

Private Sub Workbook_Open()

Application.DisplayFormulaBar = False

Application.ScreenUpdating = False

Application.MoveAfterReturnDirection = xlDown

Application.WindowState = xlMaximized

Application.EnableEvents = False

Sheets("MainMenu").Select

ActiveSheet.Range("B3").Select

Answer 1

I received a copy of the OP's project and have emailed a reply to him and the problem has been resolved satisfactorily.

The following information is for anyone else experiencing a similar problem and demonstrates that some VBA errors such as stopping on a simple line of code like selecting a worksheet are not necessarily due to a problem with the worksheet and can be due entirely to something else.

It was established that attempting to select other worksheets resulted in the same problem and therefore needed to look further than the worksheets.

There were 21 Userforms in the project. Firstly all the Userforms were exported and then removed from the project and then code re-compiled. (This entailed commenting out some code to Show the Userforms.)

Testing indicated that the problem no longer existed. Therefore each Userform was imported one at a time, the code re-compiled and retested. Two problem Userforms were identified and both of these were identical and the OP was able to confirm that one was obsolete so it was left out.

The properties of the problem Userform were recorded by opening the properties for the Userform plus each control separately and using the Snipping tool to record the exact properties. The Userform was then re-created from scratch and the remaining Userforms imported.

Initially the problem appeared to be resolved. However, the OP identified that one TextBox control had been omitted from the new Userform. It was missed because the BackStyle property was Transparent and it did not contain a value and hense was not obvious to me. (A lesson here is to use the drop down at the top of the properties dialog to identify and select each control instead of with properties dialog open and selecting each control on the Userform.) When the TextBox control was re-created, the problem returned.

The TextBox control had a ControlSource so that was removed and the problem disappeared again. Therefore it was narrowed down to the ControlSource of the TextBox. Due to the complexity of the entire project and protect and unprotect code throughout the entire VBA code rather than in a called sub, it was too difficult to totally test but I think that it was due to the ControlSource cell being protected at the time of opening the project. Therefore I tested in a stand alone workbook with just a copy of the Userform and some code in the Workbook Open event but this did not cause a problem during opening the workbook; only when the Userform was opened and if the value in the TextBox was changed.

The final solution was a work around. Remove the ControlSource property from the control and insert code in the Userform Initialize event to re-establish the property and in the Userform Deactivate event to remove the ControlSource property. With this done the entire problem was resolved.

Because I have not been able to emulate the problem in a stand alone workbook with a copy of the Userform, it is still not beyond the possibility that some other form of corruption is in the workbook and it is this that is interfering with the ControlSource property.

Answer 2

Thanks also for the feedback regarding returning user settings when exiting the program. I have mostly done that already, but I will look at the way you suggest as it appears a lot cleaner.

Answer 3

Spelling is OK, that's why the problem is so perplexing. I also tried using the sheet number to activate and that produced the same error.

Sheets(1).Select  ' Main Menu is Sheet 1

As for turning Events back on I simply did not show additional code after the problem line to keep things simple.

Do you think the problem might not lie in the code but in a bug inside the VBA itself? I use the same code to activate the MainMenu sheet in the application without a similar problem. It's only when I Compile the VBA that I get this error, and it runs fine after selecting End, and when reopening the file thereafter (till I Compile the VBA again).

Thanks

Answer 4

I have had another look at your code and thought that I should share some further information with you.

If you have other people using the workbook with the Workbook Open Event code changing their Excel options, you really should include code to save the Users existing options so they can be restored when the workbook is closed.

Some people will advise saving in global variables but it is possible to loose the global variable values;, especially if code errors occur, so I prefer to save them in a hidden worksheet.

Create a worksheet called User Options.

Then copy the code below and replace the worksheet open event and add the workbook close event to ThisWorkbook module. The worksheet "User Options" will be VeryHidden. This means that the Users cannot unhide from within the Interactive mode. The only way to unhide is with VBA code (See code at end to unhide)

Assuming that you have your code in the workbook when you open it then you will find that the options have already been changed without them having been saved. Without closing the workbook, go into options and reset them as you would normally have them for everyday use.

After setting your options to the everyday use, open the VBA editor at ThisWorkbook module and  position the cursor somewhere in the Workbook Open Sub and press F5 to run the code. This will save the normal options values in the User Options worksheet and then set them as you want them for using the workbook. It then saves the workbook.

Now when you close the workbook, your original options will be restored.

Private Sub Workbook_Open()

    Dim wsOptions As Worksheet

    Set wsOptions = Worksheets("User Options")

    wsOptions.Visible = xlSheetVeryHidden

    'Save the User Options

    With wsOptions

        .Cells(1, "A") = Application.DisplayFormulaBar

        .Cells(2, "A") = Application.MoveAfterReturn

        .Cells(3, "A") = Application.MoveAfterReturnDirection

        .Cells(4, "A") = Application.WindowState

    End With

    ThisWorkbook.Save   'Ensure the User Options are saved

    Application.DisplayFormulaBar = False

    Application.ScreenUpdating = False

    'Following line Added. If it is false then line after has no effect.

    Application.MoveAfterReturn = True

    Application.MoveAfterReturnDirection = xlDown

    Application.WindowState = xlMaximized

    On Error GoTo ReEnableEvents        'Added line

    Application.EnableEvents = False

    'Following line added to ensure worksheet will display for selecting

    Application.ScreenUpdating = True

    'Following line can help to ensure all tasks have completed

    'before attempting to select worksheet

    DoEvents

    Sheets("MainMenu").Select

    ActiveSheet.Range("B3").Select

    'Following code added. Events remain off unless turned back on with code.

ReEnableEvents:

    If Err.Number > 0 Then      'Added code

        MsgBox "Error has occurred." & vbCrLf _

            & "Commment out On Error code and re-run code to identify error line."

    End If

    Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim wsOptions As Worksheet

    Set wsOptions = Worksheets("User Options")

    'Reinstate the User Options

    With wsOptions

        Application.DisplayFormulaBar = .Cells(1, "A")

        Application.MoveAfterReturn = .Cells(2, "A")

        Application.MoveAfterReturnDirection = .Cells(3, "A")

        Application.WindowState = .Cells(4, "A")

    End With

End Sub

To unhide the User Options worksheet open the Intermediate window as described in my earlier post and enter the following code and press Enter.

Worksheets("User Options").Visible = True

Answer 5

I suggest there is a problem with the spelling of MainMenu. Could have an additional space at beginning or end of the name in the Tab name on the worksheet.

Application.EnableEvents = False turns events off and they remain off until you either turn them back on with code or you re-start Excel. Therefore I am assuming that when you close and re-open the workbook that you are not closing Excel and Events are still turned off and the Workbook Open event does not run the second time.

If turning events off with code then you need to ensure they are turned back on. Usually done with an On Error Routine but don't implement this until you have the production code ready for release or you will never be aware of errors in your code during development

To turn events back on with VBA code in the interactive mode from the VBA window.

In any VBA editing window use Ctrl and G to open the Immediate window. Now in the Immediate window type the following line and press Enter. (You won't see anything message but it does turn events back on.)

Application.EnableEvents = True

You can close the Immediate window with the cross top right of the Immediate window area.