May 14, 2011, 2:34 p.m.
posted by donnie
Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
Sadly, there is no standard feature in Excel that will enable you to protect and unprotect all worksheets in one go; however, some simple code can make it happen.
Excel provides protection that you can add to an Excel worksheet by selecting Tools Protection Protect Sheet. You also can supply a password so that another user cannot unprotect the worksheet and gain access unless he knows the password.
Sometimes, though, you want to password-protect and unprotect all worksheets in a workbook in one step because protecting and unprotecting each worksheet individually is a huge nuisance. Here is how you can simplify this task.
Open the workbook to which you want to apply the code. Or, select Window Unhide and unhide your Personal.xls file to make it available to any workbook. If this is grayed out, it means you do not have a Personal.xls file yet. You can create one easily by recording a dummy macro. Select Tools Macro Record New Macro and choose Personal Macro Workbook from the Store Macro In: box. Then click OK, select any cell, and stop recording. Excel will have created your Personal.xls file automatically.
Ensure that the textbox is still selected and then select View Properties (F4). From the Properties window of the textbox, scroll down until you see PasswordChar, and in the white box on the right, enter an asterisk (*). From the toolbox, select a CommandButton and then click the UserForm and position it in the top right of your form.
With the CommandButton still selected, select View Properties (F4). From the Properties window of the CommandButton, scroll down until you see Caption, and in the white box on the right, enter the caption OK. If you are using Excel 97, also scroll down until you see TakeFocusOnClick, and set this to False.
Now select the UserForm and, from its Properties window, find Caption and change it to Protect/Unprotect all sheets. Your form should look like that shown in Figure.
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 End Sub
The code loops through all worksheets in the active workbook. If one is protected, it unprotects it using the password entered into the text box. If the worksheet is already unprotected, it protects it using the password entered into the text box.
Sub ShowPass( ) UserForm1.Show End Sub
This is used to launch the UserForm. Close the window to get back to Excel.
Select Tools Macro Macros (Alt/Option-F8). Select ShowPass and then click Options and assign a shortcut key. This will unprotect all worksheets that are protected and protect all worksheets that are unprotected.
If you're protecting the contents only from yourself, the following macro lets you perform the same tasks with a blank password instead:
Option Explicit Sub Protect_Unprotect( ) Dim wSheet As Worksheet For Each wSheet In Worksheets With wSheet If .ProtectContents = True Then .Unprotect Password:="" Else .Protect Password:="" End If End With Next wSheet End Sub