How to lock specific cells but allow filtering and sorting

Question

I'm using the following code to lock the content of certain cells

Sub LockCell(ws As Worksheet, strCellRng As String)
  With ws
   .Unprotect
   .Cells.Locked = False
   .Range(strCellRng).Locked = True
   .Protect Contents:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, DrawingObjects:=True
  End With
End Sub

It locks the content of those specific columns. The problem is users cannot sort, neither filter, nor apply borders to the cells since those Excel menu items are disabled.

I thought the AllowSorting:=True, AllowFiltering:=True and DrawingObjects:=True would allow that the same way the AllowFormattingColumns:=True and AllowFormattingRows:=True allowed resizing.

Answer 1

There are a number of people with this difficulty. The prevailing answer is that you can't protect content from editing while allowing unhindered sorting. Your options are:

1) Allow editing and sorting :(

2) Apply protection and create buttons with code to sort using VBA. There are other posts explaining how to do this. I think there are two methods, either (1) get the code to unprotect the sheet, apply the sort, then re-protect the sheet, or (2) have the sheet protected using UserInterfaceOnly:=True.

3) Lorie's answer which does not allow users to select cells (https://stackoverflow.com/a/15390698/269953)

4) One solution that I haven't seen discussed is using VBA to provide some basic protection. For example, detect and revert changes using Worksheet_Change. It's far from an ideal solution however.

5) You could keep the sheet protected when the user is selecting the data and unprotected when the user has the header is selected. This leaves countless ways the users could mess up the data while also causing some usability issues, but at least reduces the odds of pesky co-workers thoughtlessly making unwanted changes.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Target.row = HEADER_ROW) Then
        wsMainTable.Unprotect Password:=PROTECTION_PASSWORD
    Else
        wsMainTable.Protect Password:=PROTECTION_PASSWORD, UserInterfaceOnly:=True
    End If
End Sub