Filter operation on Protected Worksheet

[Sandro]

I am wanting to make a file available to several users
whilst protecting the contents of several fields, however
in setting protection for the worksheet the pre-set data
filters do not operate. Is there some option which
requires setting for the filters to work normally, or are
filters typically locked when a worksheet is protected?

 

[Debra Dalgleish]

If you protect the worksheet programmatically, you can allow filtering
for existing Autofilters. There are instructions here:

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

 

[Dave Peterson]

It's builtin under the options in Tools|Protection|protect worksheet in xl2002+.

But if you protect the sheet in code, you can do it any version (assuming that
the filter has already been applied--arrows are visible):

This setting isn't remembered between closing/reopening the workbook. (So
Auto_open is a nice spot for it.)

Option Explicit
Sub auto_open()

Dim wks As Worksheet
Set wks = Worksheets("sheet1")

With wks
.Protect Password:="hi", userinterfaceonly:=True
.EnableAutoFilter = True
End With

End Sub