How to lock specific cells but allow filtering and sorting

excel, vba

Solution

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

Problem

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.

Original source