Protect/Unprotect all sheets in a different file

**skycowboy** · 02-23-2011, 08:08 AM

Hi,

I have found code to protect/unprotect all sheets using a command buttion and textboxes but I'd like to keep the code in a separate file from the file which I am trying to protect/unprotect sheets. I'd like to be able to use this code for any file.

Also, when password-protecting sheets, I'd like the code to request a confirmation of the password.

Below is the current code:

Private Sub CommandButton1_Click()

Dim wSheet As Worksheet

For Each wSheet In Worksheets

If wSheet.ProtectContents = True Then

wSheet.Unprotect Password:=TextBox1.Text

Else

wSheet.Protect Password:=TextBox1.Text

End If

Next wSheet

Unload Me

**Greg M** · 02-23-2011, 11:41 AM

Hi there,

First of all, you should note that your code will not "protect/unprotect ALL worksheets in a workbook", what it will actually do is to TOGGLE the existing Protected/Unprotected state of each individual worksheet. For example, if Sheets 1 & 2 are protected and Sheets 3 & 4 are unprotected, your code will unprotect Sheets 1 & 2 and PROTECT Sheets 3 & 4. I don't think this is what you intend.

You need some reference for determining whether all sheets are to be PROtected or UNprotected, and the code in the attached workbook checks the protected/unprotected state of the ACTIVE worksheet, and applies the inverse of that state to all sheets in the workbook.

The code acts upon all sheets contained in whatever workbook is currently active.

When worksheets are being protected, a "Confirm Password" textbox is displayed. If the password entered in this textbox is different from that in the "Enter Password" textbox, an error message is displayed and code execution pauses until either the correct password is entered or the entire operation is cancelled. When worksheets are being unprotected, no "Confirm Password" textbox is displayed as no "Confirm" operation is required.

I hope the above and the attached workbook are of some help. Please let me know how you get on.

Regards,

Greg M