Protection - Autofilter

[George Gee]

Hi all

Is it possible to protect the contents of a range of cells (Tools >
Protection > etc.),
but still be able to use 'Autofilter' on these cells?

Many thanks

 

[Debra Dalgleish]

If you protect the worksheet programmatically, you can allow filtering
for existing Autofilters. There are instructions on my web site:

http://www.contextures.com/xlautofilter03.html#Protect

 

[Frank Kabel]

Hi
depending on you Excel version you can allow this feature in the
worksheet protection dialog

 

[George Gee]

Thank you Deborah & Frank for your help.

George Gee

*Debra Dalgleish* has posted this message:

 

[George Gee]

Debra

How do I apply the code to more than one worksheet?
I have 15 worksheets in a workbook that I need to protect,
yet I need to allow Autofiltering on all worksheets.

Excel Version is from Office 2000.

TIA

George Gee



*Debra Dalgleish* has posted this message:

 

[Debra Dalgleish]

You could loop through the sheets in the workbook:

'==========================
Private Sub Workbook_Open()
On Error Resume Next
Dim ws As Worksheet
'check for filter, turn on if none exists
For Each ws In ActiveWorkbook.Worksheets
With ws
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next
End Sub
'==========================

Debra

How do I apply the code to more than one worksheet?
I have 15 worksheets in a workbook that I need to protect,
yet I need to allow Autofiltering on all worksheets.

Excel Version is from Office 2000.

TIA

George Gee



*Debra Dalgleish* has posted this message:

 

[George Gee]

Many thanks.

Is there not an easier way, all worksheets are not the same
and the code is treating them as such!

The code from your web site works, for one named worksheet,
is there a way to name/list the worksheets in the code?

George Gee

*Debra Dalgleish* has posted this message:

You could loop through the sheets in the workbook:

'==========================
Private Sub Workbook_Open()
On Error Resume Next
Dim ws As Worksheet
'check for filter, turn on if none exists
For Each ws In ActiveWorkbook.Worksheets
With ws
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
Next
End Sub
'==========================