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
DrawingObjects:=True would allow that the same way the
AllowFormattingRows:=True allowed resizing.
This question is tagged with
~ Asked on 2012-04-17 19:24:58
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
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
~ Answered on 2013-05-06 23:53: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
~ Answered on 2013-03-13 16:15:18