Filter protection

R

ranvin56

I have a spreadsheet with several formulas and conditional formating in place
with filters on each column. The problem is if I protect the worksheet the
filters don't seem to work. Does anyone have a suggestion?
 
J

Jim Rech

If you have Excel 2002 or later select Use AutoFilter in the Tools,
Protection, Protect Sheet dialog. Otherwise I think you're out of luck.

--
Jim
|I have a spreadsheet with several formulas and conditional formating in
place
| with filters on each column. The problem is if I protect the worksheet the
| filters don't seem to work. Does anyone have a suggestion?
 
D

Dave Peterson

If you already have the outline/subtotals/autofilter applied, you can protect
the worksheet in code (auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
'.EnableOutlining = True
.EnableAutoFilter = True
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top