Protect all sheets except one

[SaraWitch]

Hello peeps,

I am using a VBA to protect all my sheets, which works a treat. However, I would like protect them all bar one and this isn't working:

Sub protect_all_sheets()
top:
pass = InputBox("Password?")
repass = InputBox("Confirm password")
If Not (pass = repass) Then
MsgBox "You made a boo boo!"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then try again."
Next
Sheets("Sheet2").Unprotect:="1234" I think this is the problem line!
End Sub

And my multiple unprotect sheets is:

Sub unprotect_all_sheets()
On Error GoTo booboo
unpass = InputBox("Please enter the password:")
For Each Worksheet In ActiveWorkbook.Worksheets
Worksheet.Unprotect password:=unpass
Next
Exit Sub
booboo: MsgBox "There is a problem - check your password, caps locks, etc., then try again."
End Sub

Ta muchly, folks!

 

[dmt32]

Hi
you should be able to do both operations in same code

Only lightly tested but see if this update to your code helps

Sub SheetProtection(Optional ByVal ProtectSheet As Boolean)
    Dim Pass(1 To 2)    As Variant
    Dim PassMatch       As Boolean
    Dim i                         As Long
    Dim strTitle               As String
    Dim sh                      As Worksheet
   
    Const ErrMessage As String = "The password you supplied*"
    Const SheetName As String = "Sheet2"
   
    strTitle = IIf(Not ProtectSheet, "Unprotect", "Protect") & " Password"
   
    On Error GoTo myerror
   
enterpassword:
    i = 1
    Do
        Pass(i) = InputBox(Choose(i, "Enter ", "Confirm ") & strTitle, strTitle)
        'cancel pressed
        If StrPtr(Pass(i)) = 0 Then Exit Sub
        'confirm password
        If i = 2 And ProtectSheet Then
            PassMatch = Pass(1) = Pass(2)
            If Not PassMatch Then MsgBox "Passwords Do Not Match", 48, "Do Not Match"
            i = 1
        Else
            If Len(Pass(i)) > 0 Then
                If Not ProtectSheet Then Exit Do Else i = i + 1
            End If
        End If
    Loop Until PassMatch
   
    For Each sh In ThisWorkbook.Worksheets
        If ProtectSheet And sh.Name <> SheetName Then sh.Protect Pass(1) Else sh.Unprotect Pass(1)
    Next sh
   
myerror:
    If Err <> 0 Then MsgBox (Error(Err)) & Chr(10), 48, "Error"
    If Err.Description Like ErrMessage Then Resume enterpassword
End Sub

to call you add True argument to protect sheets otherwise False (optional)

SheetProtection True

Dave

 

[SaraWitch]

Hi Dave,

Thank you for your code and quick response. I don't particularly want both operations in one code as I need to unprotect sheets, make changes, then re-protect them (all except one as I have pivot table which doesn't work on a protected sheet (selected 'Use PivotTable and PivotChart' option is not retaining when running my code!).

At the moment I have assigned each macro (protect/unprotect) to buttons which work just as I would like. However, and apologies for my lack of knowledge, I'm not sure how I would run your code.

 

[SaraWitch]

I think all I would need is the code tweaking to retain the Protect Sheet options (i.e., 'Use PivotTable and PivotChart') when running the 'protect_all_sheets()' code, as this works when I protect the sheets manually - I'm not sure why they aren't when the code runs...

 

[dmt32]

Hi,
code should work in same way with each of your buttons

for the protect button call the code like this

SheetProtection True

and for unprotect button

SheetProtection False

let me know how get on

Dave