vba 如何锁定特定单元格但允许过滤和排序

How to lock specific cells but allow filtering and sorting

提问by Ronald Valdivia

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.

它锁定那些特定列的内容。问题是用户无法对单元格进行排序、过滤或边框应用，因为这些 Excel 菜单项已被禁用。

I thought the AllowSorting:=True, AllowFiltering:=Trueand DrawingObjects:=Truewould allow that the same way the AllowFormattingColumns:=Trueand AllowFormattingRows:=Trueallowed resizing.

我认为AllowSorting:=True,AllowFiltering:=True并DrawingObjects:=True允许以AllowFormattingColumns:=True与AllowFormattingRows:=True允许调整大小相同的方式进行。

回答by WoodenKitty

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

1) 允许编辑和排序:(

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.

2）应用保护并使用代码创建按钮以使用VBA进行排序。还有其他帖子解释了如何做到这一点。我认为有两种方法，要么（1）获取代码以取消保护工作表，应用排序，然后重新保护工作表，要么（2）使用UserInterfaceOnly:=True.

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

3) Lorie 的回答不允许用户选择单元格 ( 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.

4) 我没有见过的一种解决方案是使用 VBA 来提供一些基本的保护。例如，使用 检测和还原更改Worksheet_Change。然而，这远非理想的解决方案。

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.

5）您可以在用户选择数据时保护工作表，并在用户选择标题时不受保护。这留下了无数方式，用户可能会弄​​乱数据，同时也会导致一些可用性问题，但至少减少了讨厌的同事不加考虑地进行不必要更改的可能性。

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

回答by Lorie Darlin

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

• 取消保护工作表
• 转到“保护”---“允许用户编辑范围”（如果是 Excel 2007，“审阅”选项卡）
• 添加“新”范围
• 选择您希望允许用户排序​​的范围
• 点击“保护表”
• 这次，*不允许用户选择“锁定单元格”**
• 好的

http://answers.yahoo.com/question/index?qid=20090419000032AAs5VRR

http://answers.yahoo.com/question/index?qid=20090419000032AAs5VRR

回答by Isaac Moses

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.

排序和过滤不会触发 Change 事件，因此这些功能保持启用状态。

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.

请注意，此解决方案会阻止更改或清除单元格内容，但不会阻止更改格式。一个坚定的用户可以通过简单地设置要解锁的单元格来绕过它。

回答by Wisecompany

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.

Lorie 的回答很好，但是如果用户选择包含锁定和未锁定单元格的范围，则可以删除锁定/受保护单元格中的数据。

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

Isaac 的回答很好，但如果用户突出显示同时具有锁定和未锁定单元格的范围，则它不起作用。

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.

如果目标范围内的任何单元格被锁定，我稍微修改了 Isaac 的代码以撤消更改。它还显示一条消息，解释操作被撤消的原因。结合 Lorie 的回答，我能够实现所需的结果，即能够对受保护的工作表进行排序/过滤，同时仍然允许用户对未受保护的单元格进行更改。

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

按照 Lorie 的回答中的说明进行操作，然后将以下代码放入工作表模块中：

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

回答by Aviva M.

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.

要理解的是，锁定单元格的目的是防止它们被更改，并且排序会永久更改单元格值。您可以编写宏，但更好的解决方案是使用“允许用户编辑范围”功能。这使单元格可编辑，以便排序可以工作，但由于单元格在技术上仍处于锁定状态，因此您可以防止用户选择它们。

回答by Malcolm Farrelle

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

这是一个非常古老但仍然非常有用的线程。我最近带着同样的问题来到这里。我建议在适当的时候保护工作表，并在选择过滤器行（例如第 1 行）时取消保护它。我的解决方案不使用密码保护 - 我不需要它（它是一种保护措施，而不是安全功能）。我找不到识别过滤器按钮选择的事件处理程序 - 因此我向我的用户发出了首先选择过滤器单元格然后单击过滤器按钮的说明。这是我所提倡的，（我只在需要更改保护时才更改保护，这可能会或可能不会节省时间 - 我不知道，但它“感觉”正确）：

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

回答by Mike

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.

暂时取消保护工作表，过滤细胞，然后重新保护。

回答by Hank

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.

我知道这已经很老了，但是每当我用谷歌搜索这个问题时就会出现。您可以取消上述单元格中给出的范围的保护，然后向未受保护的单元格添加数据验证以引用诸如“423fdgfdsg3254fer”之类的令人发指的内容，然后如果用户尝试编辑任何这些单元格，他们将无法编辑，但您正在排序和过滤现在将工作。

回答by Graham P

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

在 Excel 2007 中，解锁要在其中输入数据的单元格。去评论

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

No VB required

不需要VB