Q:

How to lock specific cells but allow filtering and sorting

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.

A:

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

A:

This was a major problem for me and I found the following link with a relatively simple answer.  Thanks Voyager!!!
Note that I named the range I wanted others to be able to sort

Unprotect worksheet
Go to "Protection"--- "Allow Users to Edit Ranges" (if Excel 2007, "Review" tab)
Add "New" range
Select the range you want allow users to sort
Click "Protect Sheet"
This time, *do not allow users to select "locked cells"**
OK

http://answers.yahoo.com/question/index?qid=20090419000032AAs5VRR

A:

I just came up with a tricky way to get almost the same functionality. Instead of protecting the sheet the normal way, use an event handler to undo anything the user tries to do.
Add the following to the worksheet's module:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Locked = True Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
    End If
End Sub

If the user does anything to change a cell that's locked, the action will get immediately undone. The temporary disabling of events is to keep the undoing itself from triggering this event, resulting in an infinite loop.
Sorting and filtering do not trigger the Change event, so those functions remain enabled.
Note that this solution prevents changing or clearing cell contents, but does not prevent changing formats. A determined user could get around it by simply setting the cells to be unlocked.

