PDA

View Full Version : [SOLVED] Applying A Password to Most Sheets



DragonWood
06-30-2017, 04:57 PM
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
06-30-2017, 05:31 PM
Hello Dragon,

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

Paul_Hossler
06-30-2017, 06:42 PM
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
07-01-2017, 07:20 PM
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
07-02-2017, 02:42 AM
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
07-02-2017, 06:30 AM
mdmackillop,

Thank you. That worked beautifully.

Bob Phillips
07-02-2017, 07:10 AM
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
07-02-2017, 07:21 AM
xld,

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

Bob Phillips
07-02-2017, 08:55 AM
Doesn't matter, it assumes you don't that is why I use the password that you want to apply.

DragonWood
07-03-2017, 07:03 AM
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.