MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protect Sheet, but still allow sort

Posted by Jon Swanson on February 13, 2002 9:13 PM

I want to protect a sheet but still be able to use the autosort feature for the users that will be using the spreadsheet. The only reason I want to do this is to protect the formulas that I wrote...b4 through b250. the rest they can type in and sort. Let me know if there is a way to do this.....

Posted by Jon Swanson on February 13, 2002 9:23 PM

I mean use the autofilter

I mean autofilter....

Posted by Carl B on February 13, 2002 9:25 PM

Highlight all the cells that you want users to be able to use or type in. Right click, choose Format cells, go to the Protection tab, uncheck the box that says locked (leaving only the column B locked) press the ok button. Now go to Tools from the menu bar, choose protection and then Protect sheet.

Posted by Jon Swanson on February 13, 2002 9:36 PM

That does't quit do what I want.

That one I already know, but I can't filter the columns I can change...Make sense....Thanks for the help

Posted by Derek on February 14, 2002 4:10 AM

Re: I mean use the autofilter

You might have to protect your formulas a different way. You can put a data validation on column B preventing any overtyping. This however does not prevent delete or copy over. You can use conditional formatting to show when a formula has been deleted, but you have to be there to fix it up.
Most formulas can be written so they scroll down from the first formula. If this is the case, provide a macro button to scroll in the formulas. This can even be automated using a worksheet event macro.

I obtain the code by using the macro recorder. Select the top formula, delete the = sign, replace the = sign and press Enter. Now scroll your formula down. You will find the formula encoded into your macro code. Sometimes, if I need to change the formula regularly, I will make a copy in a hidden top row and get the macro to copy it back from here before scrolling.
Hope this helps
Have fun