☰

如何防止删除或更改 Excel 中的引用值？

tag-icon • tag-icon microsoft-excel data-validation
如何防止删除或更改 Excel 中的引用值？

我有一张包含唯一 ID 的 Excel 表，还有另一张表，其中包含通过值（通过数据验证 - 列出）引用第一张表中的 ID 的条目。

我已经在第二张工作表中启用了数据验证，如果第一张工作表中不存在值，则可以成功阻止在 ID 列中输入值。

但是，这样做有一个缺陷：Excel 仍然允许将无效值引入到第二张工作表的 ID 列中，方法是将 ID 值输入到第二张工作表后，在第一张工作表中删除或更改该 ID 值。

只要第二张工作表中经过验证的列中存在相应的 ID 值，我怎样才能强制 Excel 不允许更改或删除第一张工作表中的 ID 值？

答案1

正如@ForwardEd 指出的那样，这需要一些 VBA。当您的唯一 ID 列表中发生某些更改时，将触发以下代码，并检查旧值是否存在于另一个列表（您的第二张表）中。如果存在，则将撤消更改。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vOld As Variant
    Dim vNew As Variant

    If Not Intersect([ProductListItemID], Target) Is Nothing Then
        vNew = Target.Value
        Application.EnableEvents = False
        Application.Undo
        vOld = Target.Value
        If WorksheetFunction.CountIf([OrdersItemID], Target) > 0 Then
            MsgBox "Change disallowed"
        Else
            Target.Value = vNew
            'MsgBox "Change OK"
        End If
        Application.EnableEvents = True
    End If
End Sub

这应该在 Sheet1 的代码中。我假设有两个命名范围：

ProductListItemID (List of items on Sheet1 to be protected)
OrdersItemID (List of ItemID's in 2nd sheet)

为了方便起见，我将两个范围放在一张表上： 工作表样本

解释：当 Sheet1 中发生更改时，宏会检查更改是否在 ProductListItemID 范围内。如果是，它会获取更改的值 (vNew)，然后执行撤消并获取以前的值 (vOld)。然后，它会检查 vOld 值是否存在于 OrdersItemID 范围内。如果是，则单元格保留为旧值，否则，将恢复新值。

答案2

这是使用条件格式的非 VBA 替代方案。它确实依赖于用户知道该做什么，但如果您让错误变得足够明显，那么应该没问题（取决于您的用户）。

1) 添加公式以检测错误。在 Sheet1 的 B2 单元格中输入：=IF(OR(MAX(COUNTIF(ProductListItemId,ProductListItemId))>1,MIN(COUNTIF(ProductListItemId,OrdersItemID))=0),"ERROR","ok") 这是一个数组公式，因此您必须按 Ctrl-Shift-Enter，而不是直接按 Enter。公式将被 {} 包围。

2）条件格式： 条件格式 如果您将条件格式应用于整个页面或足够大的范围，则应该足以阻止用户继续操作。您可以添加指令来“撤消”更改并重试。

我应该指出，B2 中的公式还会检查产品清单（工作表 1 上的项目）中的重复项。

结果：

在此处输入图片描述

答案3

一个简单的解决方案是使用Worksheet_SelectionChange事件。在这里我要稍微大胆一点，使用罗布·盖尔准备好了。我在这个答案中重现了这张图片，以防 Rob 的答案出现任何问题。

下面的代码应该做的是，如果在 Sheet 2 中找到相应的项目 ID，则锁定单元格。然后，下次选择一个单元格时，它应该解锁单元格以供将来进行编辑。

在此处输入图片描述

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim tRangeToProtect as Range
    Set tRangeToProtect = Intersect([ProductListItemID], Target)

    If Not tRangeToProtect Is Nothing Then
        Dim tCell as Range
        Dim tItemFound as Boolean
        tItemFound = False

        For Each tCell in tRangeToProtect  ' "Target" can be multiple cells. Must always hand this.
           tItemFound = tItemFound OR WorksheetFunction.CountIf([OrdersItemID],tCell) > 0
        Next tCell
        if tItemFound Then 
            tRangetoProtect.Locked = True
            Me.Protect UserInterfaceOnly = True
            ' Use this in conjunction with worksheet.Protect UserInterfaceOnly := True
    Else
        [ProductList].Locked = False ' Open this up for future checks and editing - remove any existing locks
    End If
End Sub

这只是一个粗略的解决方案 - 可以根据您的业务环境进行改进。另外，我还没有测试过，所以您的情况可能会有所不同。

相关内容