How to Sort a Protected Table

[myfathersson]

Hi.

I'm using 2007. I want to protect worksheets containing tables to prevent inadvertant changes. However, I want to be able do things like sort tables using the filter dropdowns in the table column header row.

All cells are locked. Using the Protect Sheet dialogue from the Changes section of the Review tab, I selected a number of items in the "Allow users ...to" list, including Sort. My assumption was that the user would be able to sort the table, but Excel will not allow this. I can, however, filter.

Am I missing something? Can I sort a table in a protected, locked sheet? If not, what is the purpose of the "Allow users...to" list in the Protect Sheet dialogue? If so, how do I do it?

 

[myfathersson]

OK. I finally found my answer on the forum...just had to get the search criteria right. And the answer is...no, you cannot sort a protected, locked sheet.

Rant on.

Frankly, I think MS dropped the ball on the Protect Sheet dialogue. It really appears that you are telling Excel to allow users to perform the checked items on protected, locked cells, but, apparently, that is not what it means at all. So, if that's not what they are for, then what are they for? If they only work on unlocked selected cells, what's the use? I guess I'm just not getting it.

Rant off.

 

[Smitty]

You can record sorting on the unprotected sheet, then call that code from a button.

In the code you can add:

ActiveSheet.Unprotect "PasswordHere"
' Sort code
ActiveSheet.Protect "PasswordHere"

HTH,

 

[myfathersson]

Smitty,

Thanks very much. I'm not sure what to put in the "sort code". I sort on a variety of table columns depending on what I'm doing. Will the code you were thinking of be able to allow me to sort on one or more columns of my choosing? What I did was set up button to quickly unlock and lock the sheets. So I unlock, sort, the lock again.