autofilter & protection

dantb · Apr 17th, 2002, 08:31 PM

I was wondering if there was anyway to protect a sheet, and still be able to use the autofilter function. Thanks Dan

anno · Apr 17th, 2002, 08:40 PM

not sure if this is suitable, but you could protect your sheet and use another sheet with =Sheet1!$A$1 refs for the autofilter. you could also use an array (eg. =Sheet1!$A$1:$A$20 & ctrl+shift+enter) for the second sheet, which you can still autofilter but the user can't for instance just change one cell in the array.

dantb · Apr 17th, 2002, 08:44 PM

I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work.

Private Sub Workbook_Open()
Application.CommandBars("Worksheet Menu Bar").Controls _
("Data").Controls("filter").Enabled = True
End Sub

Qroozn · Apr 17th, 2002, 08:46 PM

will the user be looking for one set thing at a time.
eg. "SMITH"
or will they be looking for "SM......"
or blanks?

dantb · Apr 17th, 2002, 08:50 PM

I am filtering a query which is a large dbase, there are 8 different collums and many ways to search for data. I noteced the Advanced filter works with the code above, I didnt know if there was a way to get to the sub-menu Item. thx

anno · Apr 17th, 2002, 08:53 PM

Quote:

I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work.

advanced filter is still enabled with sheet protection but its requirements are different to autofilter and if your code doesn't take this into account (eg the need to enter a criteria range) maybe that's why it doesn' work. i have never used advanced filter and know nothing about vba so sorry can't advise anything.

Qroozn · Apr 17th, 2002, 08:56 PM

SOLUTION

With Worksheets("myworksheet")
.EnableSelection = xlUnlockedCells
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With

It' taken me months to get this

Qroozn · Apr 17th, 2002, 08:57 PM

put the above code in your sheets macro

dantb · Apr 17th, 2002, 09:05 PM

thanks Qroozn, I put it under my sheet1 general, but still only have the advanced filter option. Is it in the correct place ?

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

is your autofilter saved on your worksheet?
the macro should stop you being able to select anything except the autofilters already on the worksheet.

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

Apr 17th, 2002, 08:31 PM	#1
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	I was wondering if there was anyway to protect a sheet, and still be able to use the autofilter function. Thanks Dan

Apr 17th, 2002, 08:40 PM	#2
anno Board Regular Join Date: Feb 2002 Posts: 202	not sure if this is suitable, but you could protect your sheet and use another sheet with =Sheet1!$A$1 refs for the autofilter. you could also use an array (eg. =Sheet1!$A$1:$A$20 & ctrl+shift+enter) for the second sheet, which you can still autofilter but the user can't for instance just change one cell in the array.

Apr 17th, 2002, 08:44 PM	#3
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	I have a vba that will let you use the advance filter option, but cant seam to be able to make the autofilter work. Private Sub Workbook_Open() Application.CommandBars("Worksheet Menu Bar").Controls _ ("Data").Controls("filter").Enabled = True End Sub

Apr 17th, 2002, 08:46 PM	#4
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	will the user be looking for one set thing at a time. eg. "SMITH" or will they be looking for "SM......" or blanks?

Apr 17th, 2002, 08:50 PM	#5
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	I am filtering a query which is a large dbase, there are 8 different collums and many ways to search for data. I noteced the Advanced filter works with the code above, I didnt know if there was a way to get to the sub-menu Item. thx

Apr 17th, 2002, 08:56 PM	#7
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	SOLUTION With Worksheets("myworksheet") .EnableSelection = xlUnlockedCells .Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True .EnableAutoFilter = True .Protect contents:=True, userInterfaceOnly:=True End With It' taken me months to get this

Apr 17th, 2002, 08:57 PM	#8
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	put the above code in your sheets macro

Apr 17th, 2002, 09:05 PM	#9
dantb Board Regular Join Date: Mar 2002 Location: Arkansas Posts: 358	thanks Qroozn, I put it under my sheet1 general, but still only have the advanced filter option. Is it in the correct place ?

Apr 17th, 2002, 09:11 PM	#10
Qroozn Board Regular Join Date: Mar 2002 Location: Sydney/Brisbane , Australia Posts: 539	is your autofilter saved on your worksheet? the macro should stop you being able to select anything except the autofilters already on the worksheet.