VBA Change Worksheet to Workbook Question

[Pinaceous]

Hi All,

If I'm working with the following Sub that acts upon a specific sheet name its "TAB NAME", do you know how I can change this to a worksheet number?

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

If ws.Name <> "Tab1 Name" Then
'Change "TAB NAME"

If ws.Name <> "Tab2 Name" And ws.Name <> "Tab1 Name" Then

ws.visible = xlSheetHidden

Application.ScreenUpdating = False

End If
Next

Thank you for your help!
pinaceous

 

[Legacy 456155]

You can use ws.Index. However, if the user rearranges the order of the worksheets, the indices will change. Consider using the CodeName of the worksheet.

 

[JLGWhiz]

Could you be a little more clear in your objective. The code is making a name comparison. Do you want to try to make an index number comparison? Please be specific about the problem you are having if it is a problem. If you simply wish to change the type of comparison you might have some difficulty as @dataluver points out.

 

[Pinaceous]

You can use ws.Index. However, if the user rearranges the order of the worksheets, the indices will change. Consider using the CodeName of the worksheet.

Hi dataluver,

I've locked the workbook, so the user will not be changing the worksheets around.

In following through, could you please provide me with an example in using the ws.index?

Thank you,
pinaceous

 

[Legacy 456155]

Here is an example of using Index and CodeName. Even if the user is not able to rearrange worksheets, using the codename is still the less brittle choice IMO. If you later insert or delete a worksheet, it may change other worksheets' index.

Sub UsingIndex()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
    
        Select Case ws.Index
            Case 1, 2
                ws.Visible = xlSheetHidden
        End Select
        
    Next
End Sub

Sub UsingCodeName()
    Dim ws As Worksheet
    
    For Each ws In ActiveWorkbook.Worksheets
    
        Select Case ws.CodeName
            Case "Sheet1", "Sheet2"
                ws.Visible = xlSheetHidden
        End Select
        
    Next
End Sub

 

[JLGWhiz]

Just to point out that sheet code name is not always in left to right order either, and can be changed by users who have access to the vb editor. But sheet code name is a bit safer than index for sure.

 

[Pinaceous]

Here is an example of using Index and CodeName. Even if the user is not able to rearrange worksheets, using the codename is still the less brittle choice IMO. If you later insert or delete a worksheet, it may change other worksheets' index.

Sub UsingIndex()
    Dim ws As Worksheet
   
    For Each ws In ActiveWorkbook.Worksheets
   
        Select Case ws.Index
            Case 1, 2
                ws.Visible = xlSheetHidden
        End Select
       
    Next
End Sub

Sub UsingCodeName()
    Dim ws As Worksheet
   
    For Each ws In ActiveWorkbook.Worksheets
   
        Select Case ws.CodeName
            Case "Sheet1", "Sheet2"
                ws.Visible = xlSheetHidden
        End Select
       
    Next
End Sub

Thank you dataluver for sending the two codes! When words get in the way, code works wonders every time!

For my application, I'm going to use the

Sub UsingIndex()

.

Thanks again!
pinaceous

 

[Legacy 456155]

You are welcome. Thank you JLGWhiz as well.

 

[JLGWhiz]

You are welcome. Thank you JLGWhiz as well.

Happy to assist,
Regards, JLG