Auto Filter with Protected Worksheet

[JohnGuts]

Is it possible to use the auto filter functionality with a password 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.

John

[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 wrote:
>
> Is it possible to use the auto filter functionality with a password
> 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.
>
> John
>
> --
> JohnGuts
> ------------------------------------------------------------------------
> JohnGuts's Profile: http://www.excelforum.com/member.php...o&userid=30174
> View this thread: http://www.excelforum.com/showthread...hreadid=535281

--

Dave Peterson

[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...