changing sheet name and issues that go along with this...

[pingwin77]

I got my workbook programmed and doing what I need, for the most part, and realized I should change the worksheet tab names. What is the easiest way to do that with out having to rewrite all my code?

I tired changing the name of the tab and then changing all the locations the old name was in my code to the new name but kept getting errors.

[CreganTur]

If you write your code using the worksheet's code name (Sheet1, Sheet2, etc) then you won't have to rewrite your code if you rename the worksheet.

HTH:thumb

[pingwin77]

So I tried to just change the tab name from "sheet1" to "INSERT DATA" and it caused an error again. The debug highlights the line in red below. I must not be using the worksheets code name? If not, how do I do that for future reference?

Here is the code I am using:


Private Sub CommandButton1_Click()

Dim Check, Counter, Features As Range, Message, Title, Default
Dim MLRValue
Dim a, b, c, d, e, numRows, FeaturesValue, PartsValue, PartCount
Dim tgt As Range

Application.ScreenUpdating = False
'Adds variables from user form to sheet 1
With ActiveWorkbook.Sheets("Sheet1")
Sheets("Sheet1").Range("C2:F3").ClearContents
Sheets("Sheet1").Range("A9:F900").ClearContents
Sheets("Sheet1").Range("C2").Value = TextBoxPARTS.Value
Sheets("Sheet1").Range("D2").Value = TextBoxDIMS.Value
Sheets("Sheet1").Range("E2").Value = TextBoxTRIALS.Value
If OptionButton1 = True Then
Sheets("Sheet1").Range("F2").Value = "6.00"
Else
Sheets("Sheet1").Range("F2").Value = "5.15"
End If
End With



TextBoxPARTS = Val(TextBoxPARTS.Text)
TextBoxDIMS = Val(TextBoxDIMS.Text)
TextBoxTRIALS = Val(TextBoxTRIALS.Text)

a = TextBoxPARTS 'Number of Parts
b = TextBoxDIMS 'Number of Dimensions
c = TextBoxTRIALS 'Number of Trials
d = TextBoxDIMS + 4 'Total rows of data for each part
e = 1 'Part Counter

If ((a = 0) Or (b = 0) Or (c = 0)) Then '<-- verifies there are no 0's in userform
MsgBox "You can not have 0 in any field for this report and you MUST have a MINIMUM of 2 trials. Please try again.", vbOKOnly
Unload Me

End If

Select Case c '<-- prompt for minimum of 2 trials
Case Is < 2
MsgBox "You can not have less than 2 trials for a PSTDEV evaluation", vbOKOnly
Unload Me
End Select

Select Case b '<-- Adds DIMENSIONS
Case 1
Rows(8).Delete
Range("J7").Select
Unload Me
Case 2
Unload Me
Case Else
FeaturesValue = b - 2
ActiveSheet.Rows(8).Select
Selection.Copy
Do While FeaturesValue > 0 '<-- Adds Dimensions
ActiveCell.Offset(1, 0).Activate
ActiveSheet.Paste
FeaturesValue = FeaturesValue - 1
Loop
Application.CutCopyMode = False
Unload Me

End Select
Unload Me
MsgBox "Input TOTAL TOLERANCE values and adjust DIMENSION NUMBERS. When you are done, click Button #2", vbOKOnly
Range("J7").Select
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

[mumin_abdul]

Try removing

With ActiveWorkbook.Sheets("Sheet1")

and use

WorkSheets("Sheet1").Range("C2:F3").ClearContents

or select the actual workbook using its name:

workbooks("Book1").Worksheets ("Sheet2").Range("C2:F3").ClearContents

[pingwin77]

Try removing

With ActiveWorkbook.Sheets("Sheet1")

and use

WorkSheets("Sheet1").Range("C2:F3").ClearContents

or select the actual workbook using its name:

workbooks("Book1").Worksheets ("Sheet2").Range("C2:F3").ClearContents
tried that and still get the same thing, now it just goes to the next line.

here is the error I get:

Run-time error '9'

Subscript out of range

[mumin_abdul]

Edit:

I think you need to activate the worksheet before doing anything to it. So:

Workbooks("Book1").Worksheets("Sheet2").Activate

Then

Range("C2:F3").ClearContents

But qualify it with Workbooks("Book1").Worksheets("Sheet2") like this:

Workbooks("Book1").Worksheets("Sheet2").Range("C2:F3").ClearContents

So that therell be errors if someone changes the workbook while data is being processed.

[Benzadeus]

-In the VBE, go in click on Sheet1.
-Change the (name) property to sht1.

Now, change (as described in post #3)
With ActiveWorkbook.Sheets("Sheet1")
for
With sht1

[mumin_abdul]

Dont do that. It'll be more confusing.

[Benzadeus]

What about?

Private Sub CommandButton1_Click()
Const sSht1 As String = "Data input" 'change to suit
Const sSht2 As String = "Names" 'change to suit
Const sSht3 As String = "Units" 'change to suit
'and so on...

'Code
With ActiveWorkbook.Sheets(sSht1)
'Code
End With
'Code
End Sub

[mumin_abdul]

Edit: I see what you mean.

[Benzadeus]

I find #7 better than #9...

[pingwin77]

I have tried these and still have issues. It is not that big of a deal, mainly just cosmetic. Thanks for all the tips though.

[mumin_abdul]

Sorry I couldnt help.

[Paul_Hossler]

My 2 cents ...

It's often a matter of style or preferance, but I like to define such things as worksheet names as a global Const, and then Set a global Worksheet varaible when I initialize or in Workbook_Open



Const sDataSheetName as String = "UserData"
Dim wsDataSheet as Worksheet

...
...
...

Set wsDataSheet = ThisWorkbook.Worksheets(sDataSheetName)


wsDataSheet.Cells(1,1).Value = "Goes Here"



If I do decide that I need to change what I call something, it's only in one place

Paul

[rbrhodes]

[EDIT] finger slipped, double post!

[rbrhodes]

Hi ping,

The code names mentioned are the names that show up in the VBE Project Explorer window before the parentheses, eg:

Sheet1 (Sheet1)
Sheet2 (Phred)

Code name is Sheet1, Sheet name is (Sheet1), and Tab reads Sheet1
Code name is Sheet2, Sheet name is (Phred). and Tab reads Phred

To use the Sheet/Tab names in your code you would refer to:

Sheets("Sheet1")
Sheets("Phred")

Note the double quotes for the "Name"

To use code names you would use:

Sheets(sheet1)
Sheets(sheet2)

No quotes.

So what you need to do (only once) is open the VBE and note which sheet #'s are which names.

Sheet1(Al)
Sheet2(Bob)
Sheet3(Carl)
Sheet4(Dugg)
Sheet5(Etc)

With the Edit menu, click Replace.

Click the 'Current Project' radio button

Check the 'Find Whole Word Only' checkbox

In the 'replace what' box type in the Sheet name including the quotes:

"Al"

For 'replace with' put the sheet code name, no quotes:

Sheet1

Click 'Replace all'

Repeat for each Sheet/Tab name combo:

Replace: "Bob"

With: Sheet2

Do 'em all.

Save the file, and change the tab names at will! The code will recognize the Sheet codename and won't give a hoot about the whether (it's al or ali or alley...)

Of course if you delete a sheet you'll need to change your code again but that's easy to do as well. Deleting Sheet3 for instance and replacing it with a new sheet might result in the new sheet being Sheet4. Just do the Edit/Replace again for Sheet3 and Sheet5. no quotes!

Name on McDuff!


BTW: Not to confuse things but this bit of code needs some surgery:


With ActiveWorkbook.Sheets("Sheet1")
Sheets("Sheet1").Range("C2:F3").ClearContents
Sheets("Sheet1").Range("A9:F900").ClearContents
Sheets("Sheet1").Range("C2").Value = TextBoxPARTS.Value
Sheets("Sheet1").Range("D2").Value = TextBoxDIMS.Value
Sheets("Sheet1").Range("E2").Value = TextBoxTRIALS.Value
If OptionButton1 = True Then
Sheets("Sheet1").Range("F2").Value = "6.00"
Else
Sheets("Sheet1").Range("F2").Value = "5.15"
End If
End With



Should be:


With ActiveWorkbook.Sheets("Sheet1")
.Range("C2:F3").ClearContents
.Range("A9:F900").ClearContents
.Range("C2").Value = TextBoxPARTS.Value
.Range("D2").Value = TextBoxDIMS.Value
.Range("E2").Value = TextBoxTRIALS.Value
If OptionButton1 = True Then
.Range("F2").Value = "6.00"
Else
.Range("F2").Value = "5.15"
End If
End With



Imagine that the 'With' statement is there before every "."

What you have is the equivalant of:


With ActiveWorkbook.Sheets("Sheet1")
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("C2:F3").ClearContents
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("A9:F900").ClearContents
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("C2").Value = TextBoxPARTS.Value
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("D2").Value = TextBoxDIMS.Value
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("E2").Value = TextBoxTRIALS.Value
If OptionButton1 = True Then
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("F2").Value = "6.00"
Else
ActiveWorkbook.Sheets("Sheet1")Sheets("Sheet1").Range("F2").Value = "5.15"
End If
End With



Which 1) doesn't make any sense to you, me or the compiler, 2) doesn't actually provide any time savings that the 'With' is designed to do, 3) is a serious candidate for the Department of Redundancy Department(<GRIN>

HTH