protect sheet with some flexibility

[Ironman]

Hello - I have what I think is a general question on protecting a workbook/worksheet. I was hoping to have this resolved with vba code and not protecting via the options within Excel. Here's my task at hand. I have about 50 excel workbook. Each have the same columns but some have more rows of data than others, but the format is the same.

This is a "once a year" project and last year they protected the sheet via the Excel Protection options (allowing only 3 columns to be changed - everything else was protected) and sent them out. The problem was some of the end users wanted to do some more 'playing with the data', sorting, filtering...etc. These different request to send out an unprotected sheet, resulted in a 'verision' nightmere.

So now - - I'm wondering if anyone out there in Excel - Guru land has any suggestions on how to 'lock' down most columns/cells for editing while allowing for sorting and filtering? Thoughts/suggestions? I've got some VBA code that does this nicely but I'm not 100% confident in our end-user base NOT enabling the macro for use, thus making changes that would otherwise not be allowed...does this make sense?

I have a thought as I type this - - how about protecting the entire worksheet and part of the code below it's UNprotected and then the VBA code works as is. In other words, if I'm a user and I 'don't enable macros' the entire sheet is opened protected and they can't do any editing...hhhmmm? This would force them into re-opening the workbook and enabling the macros that, first step unprotects the workbook, then proceeds to only allow the group of columns for editing...again, hope that makes sense, and if anyone as thoughts/ideas to do all this without VBA that would be welcome as well...

Thanks for reading...

[excelonthegrid]

hi, in the protect sheet dialog box there is an option to allow 'Sort', does that solve your problem?

[Ironman]

I don't think so, unless I don't completely understand the protection functionality. When I tried this on a test file I can make it so B10 to B25 is unprotected, but the header is in row 10...when I attempt to sort here I get the message saying I'm not allowed. Also I'd like to have filtering avaiable and so far from what I see that is not allowed when sheet is protected.

[excelonthegrid]

the way I understand it, the allow 'Sort' property will allow you to sort on unlocked cells as long as the sort doesn't affect locked cells. usually not what people are looking for.

there is also an option to allow 'Use AutoFilter' when protecting worksheets. that could be more useful to you but you have to create the filter before protecting the sheet. AutoFilter also allows you to sort.

if that doesn't suit your needs, almost certainly a macro will be required.

[Ironman]

Thanks for your quick replies. I think I'm going with a completely protected workbook and then if a user does not enble the macro then the book will open with the sheets protected. If they select the Enable Macro then the first thing that is executed is the unprotect the sheet via the macro, then the second part of the code locks out all cells except for columns 7, 12 and 16 (or whatever I wanted to have unlocked). This setup will allow for the user to filter and sort but they wont be able to add columns or delete or change any other data in any other columns/cells. I did a quick test on this and it seemed to work ...