auto filters and worksheet protection
 

How can I use auto filters when the worksheet is protected? I have a
worksheet that I want co-workers to use, however I have formulas in certain
ranges that I don't want unwitting co-workers to change. The problem is when
I protect the worksheet, the auto filters at the top of the columns become
unusable. How can I protect the range of formulas and use the auto filters
at the same time?

I have looked in the help index for excel and can't find the answer.
Anyone's advice would be greatly appreciated.

auto filters and worksheet protection
 

Excel 2002 and newer...........

Ensure that the Autofilter is enabled before protecting the sheet.

When protecting checkmark the "Allow autofilter" option.

If earlier version you will need code to unprotect then reprotect.

From Debra Dalgleish.......................

In previous versions of Excel, use a Workbook_Open macro to set the protection
to user interface only. Store the following code on the ThisWorkbook module
sheet. It also turns on the AutoFilter is one is not in place:

Private Sub Workbook_Open()
'check for filter, turn on if none exists
With Worksheets("Data")
If Not .AutoFilterMode Then
.Range("A1").AutoFilter
End If
.EnableAutoFilter = True
.Protect Password:="password", _
Contents:=True, UserInterfaceOnly:=True
End With
End Sub

To access the ThisWorkbook module, right-click the Excel icon to the left of the
File menu, choose View Code, and paste the code where the cursor is flashing.



Gord Dibben MS Excel MVP

On Thu, 10 May 2007 20:08:00 -0700, Joanne
wrote:

How can I use auto filters when the worksheet is protected? I have a
worksheet that I want co-workers to use, however I have formulas in certain
ranges that I don't want unwitting co-workers to change. The problem is when
I protect the worksheet, the auto filters at the top of the columns become
unusable. How can I protect the range of formulas and use the auto filters
at the same time?

I have looked in the help index for excel and can't find the answer.
Anyone's advice would be greatly appreciated.

auto filters and worksheet protection
 

When you protect the sheet, click the box that says use auto-filter beside it
:)
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Joanne" wrote:

How can I use auto filters when the worksheet is protected? I have a
worksheet that I want co-workers to use, however I have formulas in certain
ranges that I don't want unwitting co-workers to change. The problem is when
I protect the worksheet, the auto filters at the top of the columns become
unusable. How can I protect the range of formulas and use the auto filters
at the same time?

I have looked in the help index for excel and can't find the answer.
Anyone's advice would be greatly appreciated.

auto filters and worksheet protection
 

You missed a step John.

Autofilter must be enabled prior to protecting the sheet.

From there move to the step you posted.


Gord Dibben MS Excel MVP


On Thu, 10 May 2007 21:08:10 -0700, John Bundy (remove)
wrote:

When you protect the sheet, click the box that says use auto-filter beside it
:)