excel - 在 Excel 中的复制粘贴事件期间不遵循数据验证规则

Question

在这个启用宏的工作表中，我对要调节数据输入的列进行了数据验证。我在这里面临的问题是，考虑到客户希望从另一个 Excel 文件复制数据的情况。实现这个场景，我已经将一些数据从国外的 excel 复制到我的数据验证启用表中。我有一列，用户只能输入小于 9 的文本长度，但是当我复制数据时说文本长度大于 9，这种情况似乎没有显示任何错误消息。有没有一种解决方法可以帮助我克服这种情况？

Answer 1

一种解决方法，因为数据验证不适用于复制/粘贴
删除 Excel 的数据验证并将其替换为您自己的验证可能是您问题的解决方案。
这允许您执行自己的验证检查。如果插入了无效数据，我们只需 .Undo插入 Action 。所以只能插入完全有效的数据。

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim AffectedCells As Range
    Dim Cell As Range
    
    'rule 1: column A allows only text length up to 9
    Set AffectedCells = Intersect(Target, Target.Parent.Range("A:A"))
    If Not AffectedCells Is Nothing Then
        For Each Cell In AffectedCells
            If Not Len(Cell.Value) <= 9 Then 'check length of each cell
                MsgBox "The data """ & Cell.Value & """ inserted in " & Cell.Address & " in column A was longer than 9. We undo!", vbCritical
                Application.Undo 'undo insert
                Exit Sub 'stop checking after one invalid data was found.
            End If
        Next Cell
    End If
    
    'rule 2: column B allows only …
    'Set AffectedCells = Intersect(Target, Target.Parent.Range("B:B"))
    'If Not AffectedCells Is Nothing Then
       'to be continued as above …
    
    
End Sub

请注意，上述解决方案有一个很大的缺点
验证范围硬编码在 VBA 代码中。例如，如果您为列 C 设置验证规则，然后在该列 C 之前插入一列，它将移至 D，但验证规则仍适用于 C。
为避免此问题，您可以使用 Named Ranges而不是硬编码的范围。因此，您在工作表中定义了一个范围，例如。给它起个名字 Rule1 (最好选择一个有意义的名称而不是编号规则)。例如标记列 C 并为其命名。
然后，您可以在...中使用该名称

Set AffectedCells = Intersect(Target, Target.Parent.Range("Rule1"))

…现在规则将坚持命名范围，您可以根据需要插入和移动范围。