Worksheet Protection

Posted by poovey on September 07, 2001 11:32 AM

Is there a way to protect contents, objects, and scenarios on ALL worksheets in a given workbook with a single password entry? We have a situation where we have 80 workbooks with 7 sheets in each book - it's a real pain in the neck to protect and unprotect each sheet anytime a change is need

Posted by Ben O. on September 07, 2001 11:55 AM

This code will protect all of your worksheets. Since the password is hard-coded, you'll also want to password-protect the module that the code is on. Otherwise you can have the code prompt the user for a password with an InputBox statement.

Private Sub ProtectAll()
For i = 1 To Sheets.Count
Sheets(i).Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="sample"
Next i
End Sub

This code will unprotect all worksheets. It will only work if all worksheets have the same password.

Private Sub UnprotectAll()
pw = InputBox("Enter Password", "Password")
For i = 1 To Sheets.Count
On Error GoTo Handler
Sheets(i).Unprotect Password:=pw
Next i
Exit Sub

Handler:
x = MsgBox("Incorrect Password", vbOKOnly, "Error")
Exit Sub

End Sub

-Ben

Posted by Poovey on September 07, 2001 12:49 PM

Ben... Thanks! That was enough info for me to put something together!