Use AutoFilter

[Rafi]

I have a protected sheet and would like the on Open event to enable the use
of Autofilters however, my code produces the following resullt

......
For Each ws In Worksheets
ws.AutoFilterMode = True
Next

Run-Time Error 1004, Application defined or object defined error

Thanks

 

[Bob Phillips]

From help

This property returns True if the drop-down arrows are currently displayed.
You can set this property to False to remove the arrows, but you cannot set
it to True. Use the AutoFilter method to filter a list and display the
drop-down arrows.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rafi]

Bob,

Thanks for your response; I think I need to better explain the situation. I
have a protected excel file posted to a SharePoint server and I want to make
sure that the users will be able to use the autofilters without having to
unprotect the file. So my idea was to write a macro that will execute on
openning of te file to enable the use of AutoFilters onn a protected sheeet.

Thanks again

 

[Gord Dibben]

You cannot change the sheet protection attributes without unprotecting the sheet
first.

To use Autofilter on a protected sheet you must have Autofilter set up prior to
protecting the sheet.

Your open code must unprotect the sheet, select a range then
Data>Filter>Autofilter then re-protect with the "use autofilter" enabled.

Sub Workbook_Open()
Sheets("Sheet1").Select
With ActiveSheet
.Unprotect Password:="justme"
.Range("A1:F1").Select
Selection.AutoFilter
.Protect Password:="justme", DrawingObjects:=True, _
Contents:=True, Scenarios:=True _
, AllowFiltering:=True
End With
End Sub


Gord Dibben MS Excel MVP