Sorting a protected shared worksheet

**Nigel** · 04-24-2009, 12:59 PM

Hi....I would like to protect some areas of a shared worksheet which has command buttons linked to macros. The macros are used mainly for sorting and its these that im having problems with. When I protect the shared workbook it wont run a sort macro from a command button. I have tried adding a line to the macro unprotecting the sheet and then another line protecting it again but I get "unprotect method of worksheet failed" error message. My ability with VB is very limited and Im using Excel 2007. Hope somebody can help with this.

Many thanks and regards.

**JBeaucaire** · 04-24-2009, 01:38 PM

Change the way your macros protect the sheets to include the UserInterfaceOnly:=True flag, or just add it into the top of your sort macros so it turns on that capability if it's not on already. Once that flag is set, macros can manipulate the protected document, though users still cannot.

Please Login or Register  to view this content.

This technique means you don't have to ever unprotect the sheet in VBA to run your macros.

**Nigel** · 04-24-2009, 02:08 PM

Thanks for the response...I will try your suggestion at work tomorrow and let you know how I get on.

Regards

Nigel

**dominicb** · 04-25-2009, 04:37 AM

Originally Posted by JBeaucaire

Change the way your macros protect the sheets to include the UserInterfaceOnly:=True flag, or just add it into the top of your sort macros so it turns on that capability if it's not on already. Once that flag is set, macros can manipulate the protected document, though users still cannot.

Please Login or Register  to view this content.

This technique means you don't have to ever unprotect the sheet in VBA to run your macros.

You can't invoke protection in a shared workbook.
It's one of Excel's wonderful restrictions.

DominicB

**Nigel** · 04-25-2009, 05:55 AM

Hi DominicB

I did try Jbeaucaire's suggestion and got the error message "Runtime error 1004 Application-defined or object-defined error" I am a little puzzled over the fact that I have managed to protect and share the worksheet, ticked the allow sorting option in the protection drop down menu and it worked fine from my PC (protected,shared and sorting from command button) but when I went to another PC in the network it gave the "workbook is in read only mode" error message when I tried to apply sort.

Anyway....thanks for the info and taking an interest in my post.

Regards

Nigel