PROTECT A SHEET AND ENABLE AUTO FILTER - Code provided

[Qroozn]

BELOW IS CODE TO ENABLE AUTOFILTER ON A worksheet when we dont want the user to adjust the sheet in any other way. I know people have always had trouble with it


With Worksheets("myworksheet")
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True,userInterfaceOnly:=True
End With
This message was edited by Qroozn on 2002-04-17 20:18

 

[Big Blue]

Qroozin - VERY useful piece of code - thanks.

Having alittle trouble getting it to work.
I've entered "sheet1" in place of "myworkbook", and I'm getting error 1004.
it doesn't like something in the .Protect method.
Any ideas?

 

[dantb]

Hi Blue, Q posted that code yesterday, works great. This is what I have in my workbook, hope it helps.

Private Sub Workbook_Open()
With Worksheets("sheet1")
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
End Sub

 

[Big Blue]

ok guys, what am I missing?
at first I thought the code would allow someone to go to a protected sheet, and set and use autofilters.
I now believe the code is designed to allow the developer to set the autofilters, THEN protect the worksheet, and have the autofilters active.

regardless-
I can't get it to work..
I've tried putting code into worksheet via VBE, and I've tried putting code into a button... no joy in either case.

please advise?