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

User · Answer

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

User · Answer

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  lt  gt  FilterRow      If NotFilterRow Then Exit For   Next c   If NotFilterRow  lt  gt  oldstate Then      If NotFilterRow Then         ws Protect      Else         ws Unprotect      End If   End If   Set ws   Nothing End Sub

User · Answer

In Excel 2007  unlock the cells that you want enter your data into  Go to Review     gt  Protect Sheet   gt  Select Locked Cells  already selected    gt  Select unlocked Cells  already selected    gt   and either  select Sort  or  Auto Filter    No VB required

User · Answer

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

User · Answer

I had a simular problem  I wanted the user to be able to filter  quot Table3 quot  in a protected worksheet  But the user is not able to edit the table  I accomplished above  using the vba code below  Range  quot Table3 quot   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  quot Table3   Headers   Aantal4   quot   Select ActiveSheet ListObjects  quot Table3 quot   Range AutoFilter Field  8  Criteria1         Array  quot 1 quot    quot 12 quot    quot 2 quot    quot 24 quot    quot 4 quot    quot 6 quot    Operator  xlFilterValues

User · Answer

If the autofiltering is part of a subroutine operation  you could use   BioSum Unprotect  letmein     lt Your function here gt   BioSum Cells 1  1  Activate BioSum Protect  letmein     to momentarily unprotect the sheet  filter the cells  and reprotect afterwards

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[excel] How to lock specific cells but allow filtering and sorting

Examples related to excel

Examples related to vba