Global Book Protection

[Mike H.]

I have a workbook with 28 sheets in it. They are formatted pretty much the
same. I have gone through all of them and set up editable ranges so users
can enter data in certain areas. But in order to protect a sheet, I have to
protect each sheet individually. I can't protect the entire workbook. If I
use the Protect Workbook option, I can globally prevent users from removing
sheets or adding sheets, but I can't use that because it also denies the
ability to hide and display forms. It seems there is nothing that does what
I want: One command, One password to prevent users from changing all sheets
but still lets my macros which display forms to run. Also, if I use the
protect workbook command it does NOT protect the uneditable areas. If I
enable sheet protection 28 times, I will make mistakes on passwords. I
already did it once, typing the upper and lower cases backwards on one sheet.
Too much password maintenance!

 

[NickHK]

Mike,
As you have found, WB protection does not achieve your aim, so you have to
use WS protection.
You can simplify the procedure, assuming you have the same PW for each
sheet:

Private Sub CommandButton1_Click()
Dim PW As String
Dim WS As Worksheet

Const STRING_ONLY As String = 2

PW = Application.InputBox("Enter the PW.", , , , , , STRING_ONLY)

If PW = "" Or PW = CStr(False) Then
MsgBox "No PW...."
Else
For Each WS In ThisWorkbook.Worksheets
WS.Protect PW
Next
End If

End Sub

And the opposite procedure to UnProtect the WSs.

NickHK

 

[Mike H]

Assuming they all share a password try this:-

Sub protect()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="mypass"
Next wSheet
End Sub


Mike

 

[Mike H.]

That is pretty cool. That is exactly what I wanted to be able to do. Thanks.