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

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.

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

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 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

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 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

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

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

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

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

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

Examples related to excel

Examples related to vba