Workbook protect all sheets

Posted by Chuck Rhodes on March 06, 2001 9:46 AM

I just want to protect all sheets in a workbook. Protect workbook does not do this. Grouping sheets does not allow protect sheet. Can I do this without VBA?

Posted by Dave Hawley on March 06, 2001 9:59 AM

Hi Chuck

I don't think so. But don't let VBA put you off! Try this.

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

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. Do the same for "UnProtectAllSheets"

Save and your done.

Dave
OzGrid Business Applications