MrExcel Publishing
Your One Stop for Excel Tips & Solutions

All Sheets in book protect macro


Posted by Jeff on December 12, 2001 4:54 PM

Ive got a multiple sheet file, and I need to ensure sheet protection (no password)is automatically enabled on all sheets before saving and exiting.
Reason being that editing may have been done on a particular sheet, where protection was forgotten to be re-enabled.
This macro needs to run if all sheets have protection.


Posted by Colo on December 12, 2001 5:15 PM

Hi! Please paste this code in "ThisWorkBook Module". But this code protect all sheets in the workbook.
:This macro needs to run if all sheets have protection.
How should I judge this?Does something sign exist in each sheet?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim sh As Worksheet
For Each sh In Worksheets
sh.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
End Sub

Posted by Jeff on December 12, 2001 6:26 PM

Thanks, I'll give this a go.
What I meant with the later comment was that the macro will not get upset when run - if all the sheets are already set as protected.

Posted by Dion on December 17, 2001 9:03 AM


I would like to do the same thing except I would like to use a password. Is this possible?