Trouble w/ Autofilter Excel 2003 protected wks

[rgarber1950]

I understand Autofilter will work with Excel 2003 when wks is protected. however I cannot figure out what I am doing wrong. Went into Tools Protection sheet and checked Allow Autofilter - but still won't work.

Thanks for any help.

Richard

[rgarber1950]

Well i figured it out - in order for Autofilter to work - I had to also check allow sort!

Richard

[rgarber1950]

Sorry - thought it was working but still is not.

Any ideas would be appreciated.

Thanks

Richard

[broro183]

The autofilter must be on (ie dropdowns visible) before the protection is applied to the sheet.

hth
Rob

[rgarber1950]

The autofilter must be on (ie dropdowns visible) before the protection is applied to the sheet.

hth
Rob

Rob
Thanks for the reply - I tried that and it still didn't work. Is it acceptable to attach the file for people to check out?

Thanks
Richard

[broro183]

hmmm, interesting...

Yes, it would be ideal if you could attach a small sample file - make sure you remove any confidential data etc.
We'll need the password to the sheet too (or it makes it easier anyway ;-)).

Rob

[rgarber1950]

I think I realize what's going on. I can actually use autofilter to select criteria from the list in a filtered column - but I can't use it to sort - even with sort allowed in wks protect.

Does sort [in Autofilter] not work when the wks is protected? Which of course is mainly what I want to be able to use.

I've attached a sample worksheet - password = "pass"

Thanks for all your help

Richard

[broro183]

hi Richard

I haven't played around too much but I think (I may be wrong) that you can only Sort "unlocked cells" (Format Cells - Protection - untick Locked before protecting the sheet) when the sheet protection is on.
I tried unlocking all cells in B5:H9, protecting the sheet & then sorting & it worked but if I unlocked all the cells & then locked say, C6 before protecting the sheet the sort wouldn't work.

Sorry that's probably not what you want to hear but fortunately, you should be able to get around it by applying "Userinterfaceonly" protection through a macro (Google will give examples), creating a button & attaching a Sort macro to it* that uses the below line of code:

Please Login or Register  to view this content.

*With the complexity of the error checks in your date formulae I'm sure it will be easy-peesy for you ;-)

hth
Rob