Sorting a range within a Protected Sheet

[bisel]

Hello All,

I have performed searches and it appears that my only solution is to use VBA to unprotect the sheet to allow sorts then re-protect once the sort is finished. Here is my dilemma.

I have a sheet with a range that is 250 rows by 17 columns. Within that range, three of the columns have formulas that I wish to prevent the user from making any modifications. But, I want to allow the user to perform sorting and filtering that will include those columns.

Here is what I have tried to do ...

• Cells in the three columns with formulas are locked
• Cells in the other columns are unlocked
• I have set up the entire range with Data Filtering.
• I have defined the entire range with a name and have set up "Allow Users to Edit Range"
• While the sheet is unprotected, the sort and filtering works as expected.
• I protect the sheet with options:
  • Select locked cells
  • Select unlocked cells
  • Sort
  • Use AutoFilter
• With the sheet protected in this manner, the filter function works fine, however the sort function yields the error , "The cell or chart you're trying to change is on a protected sheet ... "

I really appreciate any help.

Regards,

Steve

 

[mikerickson]

When you Protect the sheet, you have an option to allow the user to Sort. Is that checkbox clicked in your situation?

 

[bisel]

When you Protect the sheet, you have an option to allow the user to Sort. Is that checkbox clicked in your situation?

Yes. I select the Sort option when protecting sheet.

Steve

 

[bisel]

I found the answer to my question.

• Perform the steps as I outlined in my original post ... define range to allow users to edit
• When protecting the sheet, DO NOT select the option, "Select Locked Cells". It appears that you must leave that unchecked.
• Select the options for "Sort" and "Use AutoFilter"

The secret sauce was to un-check the "Select locked cells" option.

Steve