VBA protect multiple sheets using code names

[lydytunes]

Hoping someone is able to provide what I think is a simple solution I'm unable to find. I have a workbook with multiple sheets. Right now I currently password protect 10 of the sheets within the file. The issue I just ran into was someone change a sheet name and now the code won't work. I've not been able to find anything on line to show how to do what I'm currently doing but switch over to use the code name instead of the sheet name. Here is what my code currently looks like:

Private Sub Workbook_Open()


Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets(Array("Totals", "Item1", "Item2", "Item3", "Item4", "Item5", "Item6", "Item7", "Item8", "Item9", "Item10"))
With ws
.Unprotect Password:="tunes"
.Protect Password:="tunes", userinterfaceonly:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, AllowFormattingColumns:=True
.EnableOutlining = True
End With
Next ws
End Sub

Any thoughts on how to make this work using code name vs sheet name? I'm sure it's an easy fix, but still being a newbie to coding I'm at a loss.

 

[Jonmo1]

You can do it like this

Dim ws As Variant, MyArray As Variant
MyArray = Array(Codename1, Codename2, Codename3)
For Each ws In MyArray
    ws.Unprotect ....
Next ws

 

[JackDanIce]

This works for me, tested by changing names: Sheet1 -> Test1 and Sheet2 - Test2:

Sub test()

Dim ws  As Worksheet

For Each ws In ThisWorkbook.Worksheets(Array(Sheet1.Name, Sheet2.Name))
    ws.Unprotect "abc"
Next ws
    
End Sub

Sheet1 is the code name
Name is the name of the tab

 

[lydytunes]

Jonmo1,

Thanks for the quick response. Have updated my file with your code and everything appears to be working just fine.

 

[Jonmo1]

You're welcome.

 

[GomaPile]

Hey Guys,

Tried using the above vba and it didn't work! What am I doing wrong, can someone please look at me code!!

Private Sub Workbook_Open()
  
Dim ws As Variant, MyArray As Variant
 
MyArray = Array("Cafeteria Beverage", "Coffee Cart", "Brew on Two", "Cafeteria Food") ' Replace "Sheet Name" with the actual sheet name etc etc...
For Each ws In MyArray

    ' Check if the user is allowed to edit the sheet (based on some condition)
    If UserIsAllowedToEdit Then
        ' Unprotect the sheet with password (if required)
        ws.Unprotect "abc"
Next ws
    Else
        ' Protect the sheet with password (if required)
        ws.Range("C:C").Locked = False ' Unlocked
        ws.Range("G:G").Locked = False ' Unlocked
        ws.Protect "abc", UserInterfaceOnly:=True
    End If
UserForm3.Show    
End Sub

Cheers
NASA2 (GomaPile)