Auto Filter function

[Wardy1]

Hi,

would appreciate some help with this please. I have a detailed
spreadsheet with a autofilter so people can choose which lines to
update. I want to protect the worksheet and my formulas from any of the
users changing them. Only when I apply the protection the autofilter
stops working? Any suggestions around it?

I have tried unlocking the cells which contain the filters before
protecting the worksheet, but still doesn't work. Is there a way around
this?
regards
R

 

[Infinity]

Not sure if you have this. When protecting the spreadsheet you are
allowed to check "Use AutoFilter".

This will enable user to use auto filter even if the sheet is
protected. I believe this is only available in version 2002 and above.

 

[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
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

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