sharing and protection problem in EXCEL 3

[neiljabba]

Hello all

I have a central workbook used to collect data from more than one colleague. It has been set up to allow filtering to allow separate colleagues to filter their required data set. This has been set up using a custom toolbar with CommandBar drop down.

However the sheet is also protected so to allow the filtering to work it is unprotected filtered then reprotected. It works fine as an isolated workbook, but I have recently tried to share it to allow colleagues shared access. This has then thrown up errors in the code which arent their when its not shared.

Am I to understand that this is a problem created by the sharing and if so is it possible to share a protected workbook with these characteristics.


Many thanks in advance.

Neil

 

[GeekGirlau]

Simple answer - nope. You cannot change protection in a shared workbook.

I think you'll find that Excel 2003 allows you to set options on protecting the sheet to allow filters to be operated by all users, but if you are using earlier versions of Excel you are out of luck.

 

[xlbo]

Also have a look at the USERINTERFACEONLY argument within the protection property of a workSHEET - this will allow code to run freely but users will perceive that the worksheet is protected - this is handy for changing filters etc

Note - this argument is always set to false at workbook close so it must be reset within the workbook_open or sheet_activate events

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[DaleWatson123321]

Geoff,

Good Tip - worthy of a STAR. ;-)

Regards, Dale Watson dalwatson at gov.mb.ca

 

[GeekGirlau]

Geoff,

Great tip except for the fact that as soon as you share the workbook, you cannot change the protection property of the sheet, and therefore cannot take advantage of the UserInterfaceOnly argument - been there, done that, got the scars to prove it

 

[xlbo]

Apologies if that is the case GeekGirlau - seems to destroy the whole point of the functionality tho !

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[GeekGirlau]

In the 2003 version they made a change to the way the protection worked, so that you could nominate several differnt types of objects that could be protected. One of the options was to allow filters to operate. With earlier versions if you wanted autofilters, protection and a shared workbook, you had to pick your favourite two options, because you couldn't have all three.

 

[neiljabba]

Many thanks to all.

Nice to know that I wasnt the only one banging my head. Unfortunately I am using 2000 so until our network goes for 2003 Im stuck and Ill have to trust the users not to goof up and leave it unprotected.

Aaaaaarrrrrrrggggghhhhh!!!!!!

Thanks again everyone a very useful post by all. You deserve stars.

Cheers

Neil