如何锁定特定单元格但允许过滤和排序 [英] 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

它锁定那些特定列的内容.问题在于，由于这些Excel菜单项被禁用，用户无法对单元格进行排序，过滤或对其应用边框.

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.

我认为AllowSorting:=True，AllowFiltering:=True和DrawingObjects:=True将允许AllowFormattingColumns:=True和AllowFormattingRows:=True调整大小的相同方式.

I thought the AllowSorting:=True, AllowFiltering:=True and DrawingObjects:=True would allow that the same way the AllowFormattingColumns:=True and AllowFormattingRows:=True allowed resizing.

推荐答案

有很多人遇到这种困难.普遍的答案是，在允许无阻碍的排序的同时，您不能保护内容免于编辑.您的选择是:

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)允许编辑和排序:(

1) Allow editing and sorting :(

2)应用保护并创建带有代码的按钮，以使用VBA进行排序.还有其他文章说明了如何执行此操作.我认为有两种方法，一种是(1)获取代码以取消保护工作表，应用排序，然后重新保护工作表，或者(2)使用UserInterfaceOnly:=True保护工作表.

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答案( https://stackoverflow.com/a/15390698/269953 )

3) Lorie's answer which does not allow users to select cells (https://stackoverflow.com/a/15390698/269953)

4)我未曾讨论过的一种解决方案是使用VBA提供一些基本保护.例如，使用Worksheet_Change检测并还原更改.但是，这离理想的解决方案还很远.

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)您可以在用户选择数据时保持工作表的保护，而在用户选择标题时保持工作表的不受保护.这样就留下了无数种方式，用户可能在弄乱数据的同时还引起一些可用性问题，但至少减少了讨厌的同事不加思索地进行不必要的更改的可能性.

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

这篇关于如何锁定特定单元格但允许过滤和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！