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

I had a simular problem. I wanted the user to be able to filter "Table3" in a protected worksheet. But the user is not able to edit the table. I accomplished above, using the vba code below:

Range("Table3").Select
Selection.Locked = True
Selection.FormulaHidden = False 
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , allowfiltering:=True

In the following code I filtered the code using VBA:

Range("Table3[[#Headers],[Aantal4]]").Select
ActiveSheet.ListObjects("Table3").Range.AutoFilter Field:=8, Criteria1:= _
    Array("1", "12", "2", "24", "4", "6"), Operator:=xlFilterValues

Answer 2

This is a very old, but still very useful thread. I came here recently with the same issue. I suggest protecting the sheet when appropriate and unprotecting it when the filter row (eg Row 1) is selected. My solution doesn't use password protection - I don't need it (its a safeguard, not a security feature). I can't find an event handler that recognizes selection of a filter button - so I gave the instruction to my users to first select the filter cell then click the filter button. Here's what I advocate, (I only change protection if it needs to be changed, that may or may not save time - I don't know, but it "feels" right):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Const FilterRow = 1
  Dim c As Range
  Dim NotFilterRow As Boolean
  Dim oldstate As Boolean
  Dim ws As Worksheet
  Set ws = ActiveSheet
  oldstate = ws.ProtectContents
  NotFilterRow = False
  For Each c In Target.Cells
     NotFilterRow = c.Row <> FilterRow
     If NotFilterRow Then Exit For
  Next c
  If NotFilterRow <> oldstate Then
     If NotFilterRow Then
        ws.Protect
     Else
        ws.Unprotect
     End If
  End If
  Set ws = Nothing
End Sub

Answer 3

Lorie's answer is good, but if a user selects a range that contains locked and unlocked cells, the data in the locked/protected cells can be deleted.

Isaac's answer is great, but doesn't work if the user highlights a range that has both locked and unlocked cells.

I modified Isaac's code a bit to undo changes if ANY of the cells in the target range are locked. It also displays a message explaining why the action was undone. Combined with Lorie's answer, I was able to achieve the desired result of being able to sort/filter a protected sheet, while still allowing a user to make changes to an unprotected cell.

Follow the instructions in Lorie's answer, then put the following code in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    For Each i In Target
       If i.Locked = True Then
            Application.EnableEvents = False
            Application.Undo
            Application.EnableEvents = True
            MsgBox "Your action was undone because it made changes to a locked cell.", , "Action Undone"
        Exit For
        End If
    Next i
End Sub

Answer 4

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.

Answer 5

I know this is super old, but comes up whenever I google this issue. You can unprotect the range as given in the above cells and then add data validation to the unprotected cells to reference something outrageous like "423fdgfdsg3254fer" and then if users try to edit any those cells, they will be unable to, but you're sorting and filtering will now work.

Answer 6

In Excel 2007, unlock the cells that you want enter your data into. Go to Review

 > Protect Sheet
 > Select Locked Cells (already selected)
 > Select unlocked Cells (already selected)
 > (and either) select Sort (or) Auto Filter 

No VB required

Answer 7

If the autofiltering is part of a subroutine operation, you could use

BioSum.Unprotect "letmein"

'<Your function here>

BioSum.Cells(1, 1).Activate
BioSum.Protect "letmein" 

to momentarily unprotect the sheet, filter the cells, and reprotect afterwards.

Answer 8

Here is an article that explains the problem and solution with alot more detail:

Sorting Locked Cells in Protected Worksheets

The thing to understand is that the purpose of locking cells is to prevent them from being changed, and sorting permanently changes cell values. You can write a macro, but a much better solution is to use the "Allow Users to Edit Ranges" feature. This makes the cells editable so sorting can work, but because the cells are still technically locked you can prevent users from selecting them.

Answer 9

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

Answer 10

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