PROTECT A SHEET AND ENABLE AUTO FILTER - Code provided

Qroozn · Apr 17th, 2002, 09:01 PM

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 · Apr 18th, 2002, 09:18 AM

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 · Apr 18th, 2002, 09:36 AM

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 · Apr 19th, 2002, 12:03 PM

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 17th, 2002, 09:01 PM	#1
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	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 ]

Apr 18th, 2002, 09:18 AM	#2
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	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?

Apr 18th, 2002, 09:36 AM	#3
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	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

Apr 19th, 2002, 12:03 PM	#4
Big Blue Board Regular Join Date: Mar 2002 Location: Ontario Posts: 52	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?