Forum Discussion
Allowing Filter in Protected sheet
Rissky1 You're right, but I've got it! I tried unlocking your sheet, but the password didn't work. I used the below solution and it worked for me.
Step 1: Make cells editable so that sorting will work.
Add cells we want to sort by 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 allow 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”
Source: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_other-mso_o365b/sort-edit-range-protected-sheet-errors-in-excel/8d50d3a4-d25b-44c1-8168-c41bd87c4c57
Have you also checked the first option - select locked cells?
this should make a difference.
thanks
Peter
peteryac60 yes - I have that box checked too
Are you able to attach your workbook so I can see the issue. BTW what version of Excel are you using?
peteryac60 I am attaching a truncated version of the s/sheet. Password is the same word as in column B - Randel.
Using drop down filter, I try to sort and get the error message. Like sorting column F in A-C.
Using Excel under Microsoft Office 365 ProPlus V 16.0.11328.20392
Thanks - I understand the problem now.
I think because the cells are locked Excel won't allow you to sort.
The only way I can think to overcome this is to write some VBA code and have a button on your spreadsheet which the use can activate. The code will unlock & unprotect the sheet, sort it and then reapply the protection.
Will you allow you user to sort only on one column i.e. street address? Or are you going to allow them to sort on any column. If the latter this makes the VBA code harder.