Unprotect/protect all worksheets with a checkbox

[SaraWitch]

Hello peeps,

I have two VBAs that protect and unprotect all worksheets in a workbook with a password. Is there a way of assigning these to a checkbox, so when the checkbox is selected all the sheets are protected and when deselected they are all unprotected (upon correct password entry).

The VBAs in 'ThisWorkbook' are:

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 run this macro."
End Sub
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 lock, etc., then run this macro."
End Sub

Any help would be appreciated, thank you

 

[SaraWitch]

Actually, I just need two sheets unprotecting/protecting with a single CheckBox (I have assigned both the macros to OptionButtons for now and I have tried to add a WS Array to the formulas, but they're not working)...

 

[SaraWitch]

Further update! I've realised that the macros don't re-protect the sheet with the original permissions (i.e., Use AutoFilter, Edit objects, etc.).

I also found this:

Sub CheckBox63_Click()
With Worksheets("Placements")
If .Shapes("Check Box 63").ControlFormat.Value = 1 Then
.Protect Password:="password"
Else
.Unprotect Password:="password"
End If
End With
End Sub

But this doesn't ask for the password to unprotect, which I need, and only does the one sheet (I tried adding ,"Leavers" after "Placements" but this didn't work). This also wipes the editing permissions (e.g., Use AutoFilter, etc.).

 

[skillilea]

Somthing like this.


Private pass As String

Sub protect_all_sheets()
Dim repass As String
Dim ws As Worksheet

pass = InputBox("Password?")
repass = InputBox("Confirm password")
If Not (pass = repass) Then
MsgBox "You made a boo boo!"
protect_all_sheets
End If

For Each ws In ActiveWorkbook.Worksheets
ws.protect Password:=pass
Next
End Sub

Sub unprotect_all_sheets()
Dim ws As Worksheet
Dim unpass As String

unpass = InputBox("Please enter the password:")
If unpass = pass Then
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect pass
Next
Else
MsgBox "no match"
End If
End Sub