auto filters and worksheet protection

[Joanne]

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.

 

[Gord Dibben]

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

 

[John Bundy]

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

 

[Gord Dibben]

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