Worksheet protection with exceptions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have been using the following code to protect all worksheets in a workbook:

Public Sub ProtectAll()
Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD
Next wsSheet
End Sub

This password protects all sheets without exceptions.

Is there any way to modify the code so that it protects all sheets but it
universally allows editing of objects, autofilter, and Pivot reports?
 
If you're using Excel 2002, or later version:

Public Sub ProtectAll()
Const PWORD As String = "mysecretword"
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
wsSheet.Protect Password:=PWORD, _
DrawingObjects:=False, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next wsSheet
End Sub
 
Thanks Debra, this worked fine for me. I do have a question regarding the
syntax for setting the exceptions: why is DrawingObjects set to FALSE,
whereas the others are set to TRUE?
 
KG

Note the syntax.......Protection for Drawing Objects is set to FALSE means
"not protected"

The other two use the term "Allow" which is set to TRUE.


Gord Dibben Excel MVP
 
I attempted to use this code to fix my problem and I notice that the code
gives the user the ability to do all formatting. I have tried to change that
and I can't seem to get it. My original problem was that I want the user to
be able to change the font color while protected but not allow them to do all
other formatting. Do you have any suggestions?
 
Back
Top