Sorting a table in a protected sheet when some cells are locked

[Teapotlid]

I have a form which people will use to enter data in a standardized way.
The table has columns A:C as formulae and columns D:G as empty cells ready for people to add the necessary data using a mix of Data Validated pick lists or free-form text.
I've locked columns A:C to prevent people accidentally overwriting the formulae. Columns D:G are unlocked.
I've then protected the sheet.

Sometimes people want to sort the table to put it into a more sensible order for them to eyeball the data before they send it into me.
Even if I allow sorting when I set up the protection, it seems like a table with a mix of locked & unlocked cells can't be sorted.
I could pop a button on the sheet attached to a macro that unprotects, sorts then reprotects but is there anything easier that could be done?

I'd love to know if there is something nifty out there that I'm just not aware of.
Many thanks in advance
Teapotlid

 

[RatExcel]

That's one of Excel disfunctionalities that should be fix long time ago but it is not. Even if you check Sort and Use Autofilter options when protecting the sheet you won't be able to sort. You can only filter the values. Sorting is contected with changing the sequence of rows which basicly is changing the cells. Maybe it's possible in Excel 2013 but for sure not in 2010.

 

[Teapotlid]

Dang!
Thanks Rat. I guess I'll just have to record a macro & pop a button on my form.
Not the end of the world but it does seem a bit inelegant.

 

[PJExcel]

Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

1.Select all the cells you would like the user to be able to sort, including their column headings.
2.Go to the Data tab and click Filter. An arrow should appear next to each column header.
3.Go to Review tab-> Allow Users to Edit Ranges 1.Click “New…”
2.Give the range a title.
3.“Refers to Cells” should already contain the cells you want to allowing sorting on.
4.If you want to allow only certain people to sort, give the range a password.
5.Click “OK”


Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

1.In the “Allow Users to Edit Ranges” dialog: 1.Click “Protect Sheet…”
2.Give the worksheet a password
3.Uncheck the worksheet protection property called “Select Locked Cells”
4.Check the “Sort” property and the “AutoFilter” properties
5.Click “OK”


This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.

 

[PaulusPigus]

I have tried this solution many times and can not get it work. Although the filtering works, the data range cells can be deleted (e.g. deleted) by the user. Any suggestions?

 

[rmb4466]

Agreed. The solution posted by PJExcel essentially leaves the whole table unprotected.

I'm in the same situation as the OP: some columns using formulas that I don't want users to inadvertently edit, some data-validated columns, and some free-entry columns. I'd like my users to be able to sort and filter but still be disallowed from editing the columns with formulas. So far, the only solution I've found is the macro solution noted accurately as "inelegant" by the OP. Would love to learn of another solution.

 

[rmb4466]

Figured it out. Perhaps the solution posted by PJExcel is accurate afterall, just a bit unclear. Here's what worked for me:

- Ensure the cells you want users not to be able to edit are locked. [This is the part that I screwed up. "Step 1" made it sound to me like all cells should be unlocked.)
- Ensure the cells you want users to be able to edit are unlocked.

- Select the entire range you want users to be able to sort (including headers).
- Use Review>Allow Edit Ranges to name this selected range.

- Protect the sheet
- "Select Locked Cells" _not_ checked
- "Select Unlocked Cells" checked
- "Sort" checked
- "Use AutoFilter" checked

With this method, users won't be able to select the locked cells (without unprotecting), so won't be able to edit them. Could be a problem if users need to copy data out of the spreadsheet, but it works for my purposes.

 

[Throughtime]

That's one of Excel disfunctionalities that should be fix long time ago but it is not. Even if you check Sort and Use Autofilter options when protecting the sheet you won't be able to sort. You can only filter the values. Sorting is contected with changing the sequence of rows which basicly is changing the cells. Maybe it's possible in Excel 2013 but for sure not in 2010.

SOLUTION FOUND!

I had the same issue. Why didn't excel allow sorting of locked celled within the range selected if allow sorting was chosen? AGH!!!! Nothing worked. So, I added a button to a custom VBA! This allowed the page to be unlocked, then open the sorting box to allow sorting, then it locked it all backup again!

Here is the VBA code....

Sub SORT_LOCKED_CELLS()



On Error Resume Next

ActiveSheet.Unprotect Password:=" PASSWORD_OF_YOUR_CHOICE "

Application.CommandBars.ExecuteMso "SortCustomExcel"

ActiveSheet.Protect Password:=" PASSWORD_OF_YOUR_CHOICE ", AllowFiltering:=True

ActiveSheet.EnableSelection = xlUnlockedCells



End Sub


ALSO....Auto Filter is greyed out and you can not reset the filter to clear what columns you have sorted by. So, another button to UNFILTER.

Here is the VBA code.....


Sub ResetFilters()

On Error Resume Next

ActiveSheet.Unprotect Password:=" PASSWORD_OF_YOUR_CHOICE "

ActiveSheet.ShowAllData

ActiveSheet.Protect Password:=" PASSWORD_OF_YOUR_CHOICE ", AllowFiltering:=True

ActiveSheet.EnableSelection = xlUnlockedCells

End Sub





Hope this helps everyone!

HHickson

 

[Trebor6]

Step 1: Make cells editable so that sorting will work.

Add cells we want to sort to a range and make that range editable in “Allow Users to Edit Ranges.” This allows users to edit these cells when the worksheet is protected, even if they are locked cells.

1.Select all the cells you would like the user to be able to sort, including their column headings.
2.Go to the Data tab and click Filter. An arrow should appear next to each column header.
3.Go to Review tab-> Allow Users to Edit Ranges 1.Click “New…”
2.Give the range a title.
3.“Refers to Cells” should already contain the cells you want to allowing sorting on.
4.If you want to allow only certain people to sort, give the range a password.
5.Click “OK”


Step 2: Prevent users from editing these cells

When protecting the worksheet, uncheck “Select Locked Cells” worksheet protection property. This will prevent users from editing the cells.

1.In the “Allow Users to Edit Ranges” dialog: 1.Click “Protect Sheet…”
2.Give the worksheet a password
3.Uncheck the worksheet protection property called “Select Locked Cells”
4.Check the “Sort” property and the “AutoFilter” properties
5.Click “OK”


This solution allows users to use the Auto Filter arrows in the column names or the Sort buttons in the Data tab to sort data. Another benefit is that you have the option of allowing only certain users to sort by giving the range a password. Please note that this range password is separate from the password you set to protect the sheet.

After adding another table to the same protected sheet, the new table was sortable, but my old table was not. It was driving me nuts. Somehow, I ran into your solution and it worked perfectly! I registered just to Thank you, so THANK YOU!!