Why do my drop down filters no longer work?

[Confused KN]

I have an excel sheet with filters on it which now longer work. Previously
when I clicked on the arrows a drop down box would appear that I could select
from but now nothing happens when I click on the arrows.

When I go to Data, fliterthe auto filter box is greyed out and I cannot
selct it either.

 

[Dave Peterson]

Is the worksheet protected?

I have an excel sheet with filters on it which now longer work. Previously
when I clicked on the arrows a drop down box would appear that I could select
from but now nothing happens when I click on the arrows.

When I go to Data, fliterthe auto filter box is greyed out and I cannot
selct it either.

 

[Confused KN]

Hi Dave,

It is but I have the password to access the sheet.

 

[Dave Peterson]

Some features are disabled on protected worksheets. Changing the filter
criteria is one of those disable features.

But....

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
'If .FilterMode Then
' .ShowAllData
'End If
End With
End Sub

It needs to be reset each time you open the workbook. (Earlier versions of
excel don't remember it after closing the workbook. IIRC, xl2002+ will remember
the allow autofilter setting under tools|Protection|protect sheet, but that
won't help when you're filtering via code.)