Auto Filter with Protected Worksheet

[JohnGuts]

Is it possible to use the auto filter functionality with a passwor
protected worksheet? I want the user to be able to filter their data
but I want to protect and hide my formulas. Thanks for any help.

Joh

 

[broro183]

Hi John,

Yes, it is possible to use an autofilter on a password protected
sheet, but the filter functionality must be put in place before the
sheet is protected. There is a tickbox option for using the autofilter
in the list of things that users are to be allowed to do which appears
in the dialog box asking you to enter a password when you protect the
sheet (Well, there is in Excel 2002 & 2003 and I think there was in
2000).

To protect & hide your formulae have a look through the other options
in the password dialog box and use the normal "Format - Cells..."
approach.


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[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

 

[JohnGuts]

Thanks for the info. It worked great! I didn't scroll down far enough
to see the check box in the password protect page. Thanks again.

John

 

[broro183]

Hi John,
No problem, Thanks for the feedback pleased I could help.

Rob Brockett
NZ
Always learning & the best way to learn is to experience..