autofilter and worksheet protection

[dreamz]

for some reason, excel gives me the following error when i try to
execute an autofilter command on a protected worksheet:

run-time error '1004':

you cannot use this command on a protected sheet. etc. etc.

even though i set "allow all users of this worksheet to: use
autofilter" in the options dialog. what's wrong?

 

[Ron de Bruin]

Hi dreamz

Before you protect the sheet turn on AutoFilter

See also
http://www.contextures.com/xlautofilter03.html#Protect

 

[Guest]

Protect the sheet with userinterface only turned on. Then in the visual
basic editor for that worksheet's property box ensure that enable autofilter
is set to True. To protect with userinterface only has to be done via a
macro but it only has to be done once and then you can get rid of the macro.
Press Alt & F11 to get to the visual basic editor.

ThisWorkbook.ActiveSheet.Protect userinterfaceonly:=True

 

[dreamz]

the filter has already been created on the sheet and, since i'm using
excel 2003, i checked the "autofilter" option in the dialog. it still
does not work.

 

[dreamz]

thanks, william. worked like a charm

 

[Ron de Bruin]

Hi dreamz

Do you filter with code or manual

 

[Ron de Bruin]

See also the code on Debra's site in my reply

 

[dreamz]

i filter with code.

everything works well now. my only remaining question is this: how can
i disable cell selection with the userinterfaceonly code? i want to
make it so that the end-user can click only those cells that i
designate.