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" 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

Worksheet protection with exceptions
 

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?

"Gord Dibben" wrote:

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" 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