Error 57121 on 2003 Excel after changing Tab name 1

[Divercem]

I have a spreadsheet with a vbModeless UserForm that works for one business unit. When I open this spreadsheet and Save As to a different name I can close it and open it with no errors. But if I double-click on the first tab and change the tab name for another business unit then save the document; when I reopen it and enable macros I get a "Run-time error 57121" - Application-defined or object defined error.

If I click End then click on design mode, all the code for the sheets open in the VBAProject window. I can F8 to step through and everything seems to work fine until I save it and reopen it, then I get a Microsoft Encountered Problems and it wants to recover my data and restart Excel.

If I click Debug it takes me to the ThisWorkbook Workbook_Open event at the line - Call sBUxExt.UserForm_Initialize; but it won't let me proceed until I stop and click design mode, then again I can step through seemingly without a problem until I save and reopen it.

Here is the code:

'
' ThisWorkbook Object code
'
Private Sub Workbook_Open()
    sBUxExt.Activate
    Call sBUxExt.UserForm_Initialize
    End Sub


'
' sBUxExt Object code
'
Private Sub cmdLoadForm_Click()
    Call UserForm_Initialize
End Sub
Public Sub UserForm_Initialize()
'
'   Initialize UserForm with the values saved on sBUxExt Worksheet
'
    UserForm1.optOpenPortsHide = sBUxExt.optHidePorts.Value
    UserForm1.optOpenPortsShow = sBUxExt.optShowPorts.Value
    UserForm1.chkColB = sBUxExt.chkColB.Value
    UserForm1.chkColC.Value = sBUxExt.chkColC.Value
    UserForm1.chkColD.Value = sBUxExt.chkColD.Value
    UserForm1.chkColE.Value = sBUxExt.chkColE.Value
    UserForm1.chkColF.Value = sBUxExt.chkColF.Value
    UserForm1.chkColG.Value = sBUxExt.chkColG.Value
    UserForm1.chkColH.Value = sBUxExt.chkColH.Value
    UserForm1.chkColI.Value = sBUxExt.chkColI.Value
    UserForm1.chkSheet1.Value = sBUxExt.chkPrint1.Value
    UserForm1.chkSheet2.Value = sBUxExt.chkPrint2.Value
    UserForm1.chkSheet3.Value = sBUxExt.chkPrint3.Value
    UserForm1.chkSheet4.Value = sBUxExt.chkPrint4.Value
    UserForm1.chkSheet5.Value = sBUxExt.chkPrint5.Value
    UserForm1.chkSheet6.Value = sBUxExt.chkPrint6.Value
    UserForm1.chkSheet7.Value = sBUxExt.chkPrint7.Value
    UserForm1.chkSheet8.Value = sBUxExt.chkPrint8.Value
    UserForm1.txtSheet1.Value = sBUxExt.Range("P15").Value
    UserForm1.txtSheet2.Value = sBUxExt.Range("P16").Value
    UserForm1.txtSheet3.Value = sBUxExt.Range("P17").Value
    UserForm1.txtSheet4.Value = sBUxExt.Range("P18").Value
    UserForm1.txtSheet5.Value = sBUxExt.Range("P19").Value
    UserForm1.txtSheet6.Value = sBUxExt.Range("P20").Value
    UserForm1.txtSheet7.Value = sBUxExt.Range("P21").Value
    UserForm1.txtSheet8.Value = sBUxExt.Range("P22").Value
    UserForm1.txtInfo.Value = sBUxExt.Range("P24").Value
'   Set the window values
    sBUxExt.Activate
    With ActiveWindow.VisibleRange
        UserForm1.Top = sBUxExt.cmdLoadForm.Top
        UserForm1.Left = sBUxExt.cmdLoadForm.Left
    End With
'   Now load the userform
    Load UserForm1
    UserForm1.Show (modeless)
End Sub

Instead of the default Sheet1, etc, all are named, the first worksheet is named sBUxExt.

The UserForm has 30+ objects on it and the code is considerably longer.

Any help would be appreciated so I don't pull the rest of my hair out...

Charlie

 

[PHV]

If I click End
Which line of code is highlighted if you click Debug instead ?

Seems like you confuse Worksheet.Name and Worksheet.CodeName

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Divercem]

Thanks for the reply PH.

I know my post was long, in the paragraph after "If I click end" is: [red]If I click Debug it takes me to the ThisWorkbook Workbook_Open event at the line - Call sBUxExt.UserForm_Initialize; but it won't let me proceed until I stop and click design mode, then again I can step through seemingly without a problem until I save and reopen it.[/red]

You are right about me being confused. Since I didn't really know what Worksheet.CodeName was I did some additional research to make sure how to answer your statement. As I understand it Worksheet.CodeName is "Sheet1" at design time and can be changed through the (Name) field in the properties window or through code similar to ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "sBUxExt". And Worksheet.Name is what shows up on the tab of a worksheet and it can be changed through Name field of the properties window or through code similar to Worksheet(1).Name = "Fueling by Ext. "

In the properties window I can change Worksheet.CodeName (and change references in the code to match) without experiencing any problems. But I see the error when I change Worksheet.Name. I have tried it a couple of different ways with the same result. 1) I double-click directly on the "Fueling by Ext" tab name and typing "WTS by Ext" and 2) I open the properties window and type "WTS by Ext" in the Name field.

Thanks,

Charlie

 

[SkipVought]

sBUxExt is a literal, according to you. Yet you are using it an a OBJECT...

    UserForm1.optOpenPortsHide = [b]sBUxExt[/b].optHidePorts.Value

Let's review.

Your Sheet Name is sBUxExt.

Unless you have changed the CodeName, the CodeName is most likely Sheet1.

So here are your code options...

    UserForm1.optOpenPortsHide = [b]Sheets("sBUxExt")[/b].optHidePorts.Value

or

    UserForm1.optOpenPortsHide = [b]Sheet1[/b].optHidePorts.Value

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Divercem]

Thanks Skip

When I change all code to Sheets("sBUxExt").expression I get run-time error '9': Subscript out of range.

When I change all code to sheet1.expression I get run-time error '424': Object Required

Since I don't know how to copy the Properties Window here's a text representation:

[tt]Properties - sBUxExt (Window Title)
sBUxExt Worksheet
(Name) sBUxExt
DisplayPageBreaks False
DisplayRightToLeft False
EnableAutoFilter False
EnableCalculation True
EnableOutlining False
EnablePivotTable False
EnableSelection 1 - xlUnlockedCells
[red]Name Fueling by Ext[/red]
ScrollArea
StandardWidth 8.43
Visible -1 - xlSheetVisible[/tt]

Name is what I have been changing to WTS by Ext and saving. I don't get the error until I open it again.

...I found this information on another webpage describing what I'm experiencing as a Microsoft bug - another webpage

Thanks,

Charlie

 

[SkipVought]

FYI, I almost always use the CodeName, or some other implicit method to reference a sheet, ESPECIALLY if the Sheet Names will be changed.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Divercem]

PH & Skip,

I typically avoid using the Sheet Name and use what I now know is the Sheet CodeName so I started at the first textbox object in the UserForm and looked at the Properties Window and noticed that ControlSource is referencing 'WTS by Ext'!b1 (the name did change from 'Fueling by Ext'!b1).

I removed all references out of ControlSource fields for all the objects. Then changed the Worksheet.Name, saved it, closed it, and reopened it without any errors.

Thanks for your time and gingerly guiding me the differences between CodeName and Name.

Charlie