Allowing sorting on a protected sheet

[STEVENS3010]

Hi,

I have a code where it allows filtering on a protected sheet but when I'm trying to add the allow sorting feature I get the 'cannot edit protected sheet error message'.

The VBA code I'm using is below - I cannot see where I'm going wrong; can anybody help?

With ActiveSheet
    .Protect Password:="", AllowFiltering:=True, AllowSorting:=True
End With

 

[rlv01]

You have to unlock the sort range

With ActiveSheet
    .Range("A1:A100").Locked = False                  'unlock the cells you want to sort
    .Protect Password:="", AllowFiltering:=True, AllowSorting:=True
End With

 

[STEVENS3010]

Thanks for the reply. The issue I have is that is the data I want to keep protected.

 

[rlv01]

Well the idea behind a protected sheet is to restrict modification, and sorting is modification from the perspective of an individual cell. You have a couple of options. One is to control sorting via VBA. That way you can protect and unprotect as needed. The other is to create a copy of the table or range and allow sorting on the copy, not the original. You could also unlock the cells, but then prevent any cells from being selected on the protected sheet.

 

[STEVENS3010]

Thanks for your advice. None of those options really work for the type of sheet I have. I guess I'll just have to live without the sort function. It's really weird as the filter option works but the sort doesn't?

 

[rlv01]

It's really weird as the filter option works but the sort doesn't?

Not so weird. The filter function does not modify data in a given cell. It just displays it or not. When you sort, you rearrange data. You may not think of it as changing the data, but the act of sorting changes the contents of cells.

 

[STEVENS3010]

Fair point