在Excel中的复制粘贴事件期间未遵循数据验证规则 [英] Not following data validation rules during copy paste event in excel

问题描述

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

In this macro enabled sheet i have data validations for columns where i want to regulate the data input. The problem i am facing here is that, Considering scenario where the customer wishes to copy data from another excel file. Implementing this scenario i have copied some data from a foreign excel to my data validations enabled sheet. I have a column where the user can input only text length less than 9 but when i copy the data say text length greater than 9, such scenarios do not seem to show any error message. Is there a workaround which would help me overcome this situation?

推荐答案

一种解决方法，因为数据验证不适用于复制/粘贴

删除Excel的数据验证并将其替换为您自己的验证可以解决您的问题.

A workaround since Data Validation doesn't work with copy/paste

Remove Excel's data validation and replace it with your own validation could be a solution to your issue.

这使您可以执行自己的验证检查.如果插入了无效数据，我们只需.Undo插入操作.因此只能插入完全有效的数据.

This allows you to perform your own validation checks. And if non-valid data was inserted we just .Undo the insert action. So only completely valid data can be inserted.

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 <=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.

Note that the solution above has one big disadvantage

The validation ranges are hard coded in the VBA code. If you eg set a validation rule for column C and then you insert a column before that column C it will move to D but the validation rules still apply to C.

为避免此问题，您可以使用

To avoid this issue you can use Named Ranges instead of hard coded ranges. So you define a range in your sheet and eg. name it Rule1 (better choose a meaningful name instead of numbering rules). For example mark column C and give it a name.

然后您可以在…中使用该名称

Then you can use that name in …

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

...，现在规则将遵循指定的范围，您可以根据需要插入和移动范围.

… and now the rules will stick to the named range and you can insert and move your ranges as you like.

这篇关于在Excel中的复制粘贴事件期间未遵循数据验证规则的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！