Forum Discussion
Excluding Sheets, Help Please!
Try out this code
Also I'd encourage you not to reference cells directly in your VB code (e.g. B2) and instead used Named Ranges (e.g. Go to Formula > Define Name > call it wksSheetName and set the Scope box to that sheet name). Then repeat for each sheet.
In your code you then refer to wks.Range("wksSheetName").value and your code won't break if someone inserts / deletes rows or columns moving "B2" to another cell.
Sub RenameTabs()
'Updateby20140624
Dim wks As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each wks In Worksheets
Select Case wks.Name
Case "Directions"
GoTo Skip
Case "Tips & Tricks"
GoTo Skip
Case "Home"
GoTo Skip
Case "Bubble Kids"
GoTo Skip
Case Else
If wks.Range("B2").Value <> "" Then
wks.Name = wks.Range("B2").Value
End If
Skip:
End Select
Next wks
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
You can allow for multiple values in case statements to simplify code:
For Each wks In Worksheets
Select Case wks.Name
Case "Directions", "Tips & Tricks", "Home", "Bubble Kids"
GoTo Skip
Case Else
If wks.Range("B2").Value <> "" Then
wks.Name = wks.Range("B2").Value
End If
End Select
Skip:
Next wks
Definitly use Select statements which have the same output on the same line, like Matt shows. You can use the assumed syntax, or specify it:
Case Is = Condition
Or
Case Condition
Regarding the rest of your code, there are potential pitfalls. You reference the Sheets collection, which is all worksheet types (i.e. worksheets, chart sheets, macro sheets, etc.), and then reference the Worksheets collection which is only the Worksheet type of these objects. In other words, if you have, for example, a Chart sheet in your file, you'll possibly have skewed results because the two counts/indexes do not match. If you want to reference Worksheets, use that. If you want to reference Sheets, account for that in your code (the possibility of other sheet types), but be consistent whichever you choose. I suspect you want to use the Worksheet collection, which is used 99.99% of the time.
You also don't reference a workbook. It's assumed then that it is the ActiveWorkbook. This happens because VBA is very forgiving, unlike other languages (e.g. C#). This doesn't mean you shouldn't do it, as it's good programming practices to explicitly declare all objects. I recommend declaring the workbook, even if it's the ActiveWorkbook or ThisWorkbook. Additionally, when referencing the Range object, the same principle applies. If no Range parent object (e.g. Worksheet object) is declared, the ActiveSheet is assumed. Assumptions can lead to problems down the road.
HTH
Thank you for the help. I've actually decided against using the macro to rename the sheets because it does not update immediately like I need it to. Macros are something for me to explore further and I just don't have the time to fiddle and make it work. :)
Ah, if you want immediate updating you need an event! You can forego the previous code in a standard module. You need to go into the 'ThisWorkbook' module and paste this event code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If TypeName(Sh) = "Worksheet" Then If Sh.Name <> "Directions" And Sh.Name <> "Tips & Tricks" And Sh.Name <> "Bubble Kids" Then If Target.Address(0, 0) = "B2" Then If Me.Name <> Target.Value Then Application.EnableEvents = False On Error Resume Next Sh.Name = Left(Target.Value, 31) On Error GoTo 0 Application.EnableEvents = True If Err.Number <> 0 Then MsgBox "The worksheet name couldn't be updated. Make sure there aren't any illegal characters in the value.", vbExclamation + vbOKOnly End If End If End If End If End If End Sub
