With EXCEL 2K,I need use autofilter while protecting sheet, how?

S

Song

With EXCEL 2K,I need use autofilter function while protecting a sheet, how to
make it happen?
 
J

Jennifer

You can right click on the auto filter button and click
Format cell
Select protection tab and make sure it is NOT locked. Now when you protect
the sheet it should allow the filter to work while still protected. Hope this
does the trick. Jennifer
 
S

Song

Thanks, Jennifer, but the filter button doesn't work once you protect the
sheet. I am using excel 2000, I am not sure if it has sth relation to the
version.
 
J

Jennifer

Sorry, I am using 2003 and I tried it and it worked with no problems. Not
sure about the 2k. When you protect the sheet does it prompt you to select
locked and unlocked cells? Sorry, no help. Jenifer
P.S. When you say the filter button you are refering to a button you created
that is a macro that performs your filter right. If not, that is the problem.
 
S

Song

"filter" means the function under data menu...
With excel2000, no pop-up for the selection of locked and unlocked cells...
 
J

Jennifer

Ok, you need to create a macro that performs the auto filter function for
you. This is an example of what the macro may look like:
Sub Macro7()
ActiveWindow.SmallScroll Down:=0
Range("D11").Select
Range("A39:K274").AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("A21:A22"), Unique:=False
ActiveWindow.SmallScroll Down:=-23
End Sub

This macro is attached to a button that anyone can push and it will perform
the auto filter. Let me know what you need next. May want to give me an
example of what you want to filter, may be able to write the macro for you.
Probably not tonight though. Jennifer
 
B

Biff

Hi!

I'm not sure about XL2K but in XLXP there is an option to
allow autofilter when the sheet is protected.

In XLXP - goto Tools>Protection>Protect Sheet.

A dialog opens with all the protection options. Scroll
down the list and select USE AUTOFILTER.

Biff
 
S

Song

I understand the macro you wrote is same as "advanced filter" under data
menu, which can work while the sheet being protected, but what I need is the
"auto filter" function.
 
M

Max

Song said:
I understand the macro you wrote is same as "advanced filter" under data
menu, which can work while the sheet being protected, but what I need is the
"auto filter" function.
Click to expand...

Maybe you'd like to check out Debra Dalgleish's page at:
http://www.contextures.com/xlautofilter03.html#Protect

Look for the section: AutoFilter on a Protected Worksheet
(it's near the bottom of the page)

There's a Workbook_Open macro provided
which you could try .. (Full instructions given there)
 
Top