Protecting Sheet in Shared Mode AND using a filter macro

**Amanda81** · 10-17-2011, 09:40 AM

I have a large spreadsheet that will be saved to a shared network for multiple users to access and update. I have 3 things that I would like the spreadsheet to do, that I cannot figure out how to get them ALL to work at the same time.

1. The spreadsheet must in in Shared mode to allow multiple users to edit at the same time.
2. I also added some simple code to reset the auto-filters each time the spreadsheet is opened. The users of the spreadsheet are not advanced Excel users in many cases. Each user would be instructed to filter the spreadsheet to only show their items, and then to make the changes to those assigned to them. Obviously, they wouldn't likely clear the filter before saving, and the next person opening the file could be confused.
3. Finally, I would like to protect the sheet so that only the 2 input columns where the users are being asked to make their changes, would be unlocked, protecting the rest of the document from being changed/deleted.

I can get #1 and #2 to work together, but when I try and add the protection, the autofilter clear code doesn't work. My guess is that the macro won't clear a filter on a column that is read-only.

Is there a code that would allow me to clear the autofilters, and then apply the protection to the worksheet?

Thanks for your patience in advance. I'm very new to VB and macros.

Amanda

**romperstomper** · 10-17-2011, 09:45 AM

You cannot change the protection in a shared workbook, so I'm afraid you are out of luck. I would also strongly advise you not to use Excel if you truly need multi-user access to data. Shared Workbooks are notorious for bloat, corruption, data loss and other strange behaviour.

**Amanda81** · 10-17-2011, 09:59 AM

Thanks for the quick reply romper. Unfortunately I don't have the option of using anything other than Excel - there is a precedence of using it for this sort of project that I don't think I can change, plus the users don't have access to other programs (and surely wouldn't know how to use them anyway). I can protect the sheet, and then put it in shared mode, which will work, but without the filter clearing.

In addition, I can track changes, which would at least let me know WHO deleted half the workbook. :-)
And, I can just stress in the instructions to the users, that they should only make changes to columns A and B, don't delete any rows or columns, etc, and hope they follow the directions.

Any other suggestions? Given this scenario, what's your opinion on what should take priority -- the macro for clearing the auto-filters or protecting the sheet, if I can only do one or the other?

Thanks!

**Richard Buttrey** · 10-17-2011, 10:06 AM

Hi Amanda,

In my opinion shared workbooks tend to create more problems than they solve. As far as I'm aware you can't change worksheet protection in a shared workbook. You'll need to choose between filtering / protecting and sharing.

I suggest the first thing you do is review the overall requirement and ask yourself is it really necessary to share the workbook? Is it not possible for users to have their own individual workbooks, and then have a master summary workbook that gathers data from individual workbooks.

In a development I undertook last year where the client was insistent on a shared workbook, but where users were only adding their own data and not changing common data, the approach I adopted was to have individual input sheets for the users and a master worksheet which automatically accumulated data as users changed their own sheets. The summary sheet was not protected and allowed filtering. The downside is that when changes are required to the structure the administrator has to remove the workbook from shared use, and of course no input by users is then possible until it's been reset.

Regards

**romperstomper** · 10-17-2011, 10:09 AM

It's hard to be specific without knowing the exact purpose of the workbook, but you can use a separate workbook as a data store and retrieve/update data in that from a 'front-end' workbook.
If you need to stick with a simple shared workbook, then I'd keep the protection, though you can specify to allow autofilter usage in a protected sheet.
Oh, and make sure you have frequent backups.