protect and autofilter

[faxof]

hi
i have my sheet protected using the vba command

worksheet(i).protect password:="secret" userinterfaceonly:="True"

now i cant seem to use the autofilter command

is tehre anyway to use the audo filter and have a protected worksheet.

maybe a button that unprotects, uses autofilter then protects again, or something like that

please help

quizzed
faxof

 

[xlbo]

With userinterfaconly = true - you can run code without protect / unprotect so you only need to code the autofilter
Guess the question is whether it is a specific set of fields that needs to be filtered or whether it is open to the user to decide.... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[faxof]

hi geoff

i did try that - i can turn autofilter on with code; when i go to the sheet the autofilter is there but i can't click on the down arrows. (well i can but they dont do anything.)

in defiance i tried to use the worksheet commands:
on worksheet change select i added the target value to a variable string called dontChange. then used the worksheet change to send dontChange to the target cell value.

this works ok but not when i change a range of cells
also - users can still insert rows - how do i stop them inserting rows


thanx for your help

faxof

 

[xlbo]

It's a bit of a catch 22. If you want worksheet protection, you can't use an autofilter. You can put it there but it won't be usable whilst the sheet is protected. Therefore, for users to be able to use the autofilter, they will also be able to dlete / insert rows etc - anything your protection is guarding against. I'm sure it is doable but it would require a LOT of code around certain events and I'm pretty sure that users would still be able to break it. My suggestion......either lose the protection or lose the autofilter. You could always have a replica set of data on another sheet that is generated by code and autofilter that..... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde