Worksheet protection with exceptions

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


KG wrote:
> 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?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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?

"Debra Dalgleish" wrote:

> 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
>
>
> KG wrote:
> > 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?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

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

On Wed, 9 Feb 2005 07:01:04 -0800, "KG" <KG@discussions.microsoft.com> wrote:

>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?
>
>"Debra Dalgleish" wrote:
>
>> 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
>>
>>
>> KG wrote:
>> > 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?
>>
>>
>> --
>> Debra Dalgleish
>> Excel FAQ, Tips & Book List
>> http://www.contextures.com/tiptech.html
>>
>>