MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protecting and Unprotecting All Workbook Sheets


Posted by Glenn on March 29, 2001 11:49 AM

I want to be able to have a pair of buttons on my toolbar that allows me to protect and unprotect ALL the sheets in a workbook.

An earlier post provided the methodology to do it for ONE workbook. What can I do so that I can have a toolbar button so that I can do this for ANY workbook.

Below please find the methodology:
Push Alt+F11 and go to Insert>Module, paste in this code:

Dim Shts As Worksheet
Sub UnProtectAllSheets()
'Unprotects ALL worksheets
For Each Shts In ThisWorkbook.Worksheets
Shts.Unprotect Password:=secret
Next
End Sub

Push Alt+Q.

Now Push Alt+F8 and click "UnprotectAllSheets" then "Options" and assign a shortcut key.

Sub ProtectAllSheets()
'Protects ALL worksheets
For Each Shts In ThisWorkbook.Worksheets
Shts.Protect Password:=secret
Next
End Sub


Push Alt+Q.

Now Push Alt+F8 and
click "ProtectAllSheets"
then "Options" and assign a shortcut key.


Thank You
Glenn


Posted by Dax on March 29, 2001 1:42 PM

The easiest thing to do is to save these macros in your Personal workbook and change all occurences of the word ThisWorkbook to ActiveWorkbook. Then add a couple of buttons to your toolbar and assign the macros to these buttons.

Posted by Glenn on March 29, 2001 5:15 PM

Dax-

Bingo!! Thank you for your help.