Applying A Password to Most Sheets

[DragonWood]

Greetings,


I have this wonderful little code that I found on this site a couple years ago. It has worked wonderfully everytime.


However, now I need to make a small change to it and I'm not sure how to go about that.


I have a couple sheets in my project that were imported from another source and are already password protected. I need the below code to ignore those sheets when applying this password.


I'm pretty sure I could use an Array of some kind to simply list the sheets I want to skip when applying the password. However, every example I have been finding has been providing a way to list the pages I want to apply the password to. Since there are only a couple that don't need it, and several that do, with the potential to have more added as the project progresses; I would like to just skip the ones I don't want to apply the password to.


I would appreciate any assistance I can get with this.

Private Sub Workbook_Open()
'Sets the password for each Worksheet, but still allows the code to work.


    Dim wkSheet As Worksheet


    For Each wkSheet In Worksheets
        wkSheet.Protect "Password", UserInterfaceOnly:=True
    Next wkSheet


End Sub

Thanks.

[Leith Ross]

Hello Dragon,

Will the sheet names that need to be skipped remain constant?

[Paul_Hossler]

If the WS is not already protected, this will apply the password

Option Explicit
Private Sub Workbook_Open()
     'Sets the password for each Worksheet, but still allows the code to work.
     
     
    Dim wkSheet As Worksheet
     
     
    For Each wkSheet In Worksheets
        If Not wkSheet.ProtectContents Then
            wkSheet.Protect "Password", UserInterfaceOnly:=True
        End If
    Next wkSheet
     
     
End Sub

[DragonWood]

Leith,

Yes, the names of the sheets to be skipped will remain constant. So, I just need to know how to tell the code to skip them when applying the password. For right now, let's just call them Sheet1, Sheet2, and Sheet3.

Thanks.

[mdmackillop]

Option Explicit
Private Sub Workbook_Open()
     'Sets the password for each Worksheet, but still allows the code to work.
    Dim wkSheet As Worksheet
    For Each wkSheet In Worksheets
    Select Case wkSheet.Name
        Case "Sheet1", "Sheet2", "Sheet3"
            'do nothing
        Case Else
            wkSheet.Protect "Password", UserInterfaceOnly:=True
    End Select
    Next wkSheet
End Sub

[DragonWood]

mdmackillop,

Thank you. That worked beautifully.

[Bob Phillips]

Another approach h

Private Sub Workbook_Open()
     'Sets the password for each Worksheet, but still allows the code to work.
     
     
    Dim wkSheet As Worksheet
     
     
    For Each wkSheet In Worksheets
    
        On Error GoTo protected
        wkSheet.Unprotect "Password"
        
        wkSheet.Protect "Password", UserInterfaceOnly:=True
protected:
        On Error GoTo 0
    Next wkSheet
End Sub

[DragonWood]

xld,

Thank you. That would work too, except I don't know the password to the pages I'm skipping.

[Bob Phillips]

Doesn't matter, it assumes you don't that is why I use the password that you want to apply.

[DragonWood]

xld,

I gave it a try, I get an error when it tried to apply the password. I like the concept, however, I have a couple other pages that I don't want a password on at all because they have ever expanding tables from user input. So, the skipping certain pages code will actually work better in this case.

Thanks anyway.