数据验证如何在Excel中复制/粘贴不同来源的数据时使用

问题描述：

我的Excel表中有5个不同的列，每个列都有单独的数据验证规则。当用户通过键盘手动输入时，我的规则正在工作。
但是，虽然复制粘贴来自不同来源的数据，如notepad,one note等我的验证不起作用。只有当您单独点击cell时才有效。
例子：我的专栏都喜欢， Name, Employee ID, Plan ID, Client Name, Email ID等数据验证如何在Excel中复制/粘贴不同来源的数据时使用

我需要某种VBA或公式在我的数据验证自动工作，当用户拷贝/从不同的源粘贴数据。

看到这里的例子，如果规则将被覆盖显示一条消息https://stackoverflow.com/questions/29386971/force-pasted-values-to-obey-data-validation-rules – QHarr

答

是的，我遇到了同样的问题。我通过阻止粘贴来解决它。在该模块中我有一个代码：

Sub NotAllowPaste() 
Dim UndoList As String 
If ThisWorkbook.Name <> ActiveWorkbook.Name Then Exit Sub 
With Application 
    .EnableEvents = False 
    UndoList = .CommandBars("Standard").Controls("&Undo").List(1) 
    If InStr(UndoList, "Paste") > 0 Or _ 
    UndoList = "Keep Source Formatting" Or _ 
    UndoList = "Drag and Drop" Then 
     .Undo 
     MsgBox "Pasting and ""drag and drop"" is forbidden in this workbook.", vbCritical 
    End If 
    .EnableEvents = True 
End With 
End Sub

然后，在工作表中的代码，我已经把：

Private Sub Worksheet_Activate() 
    Application.DisplayFormulaBar = False 
End Sub 

Private Sub Worksheet_Change(ByVal Target As Range) 
    NotAllowPaste 
End Sub 

Private Sub Worksheet_Deactivate() 
    Application.DisplayFormulaBar = True 
End Sub

正如你所看到的，我已禁用公式栏还可以防止用户直接复制到它。这个对我有用。

感谢你的投入。 –

如果我错过了任何东西，请纠正我。你们为我提供了防止复制/粘贴的解决方案。但是，我的要求是允许用户粘贴来自不同来源的经过验证的工作表。用户不能每次都手动创建记录。假设我们有从数据库或外部平面文件中提取的1000多条记录。用户想要将这1000条记录粘贴在我的工作表中，并希望验证。请为此提供任何解决方案。 –

这取决于您需要什么样的验证。数据应该放在任何列表中，或者是数字，在某个范围内，或者以大写字母开头，或者其他什么。 – MarcinSzaleniec

答

子程序检查对列表中，正常的模块中：

Sub ListToCheck(rng As Range) 
Dim cl As Range 
Dim i As Integer 
Dim bMatch As Boolean 
Dim sListName As String 

sListName = "sheet2!MyList" 'change this accrording to your needs 
bMatch = False 

For Each cl In rng.Cells 
    With WorksheetFunction 
    For i = 1 To .CountA(Range("MyList")) 
     If cl.Value = .Index(Range(sListName), i) Then bMatch = True 
    Next i 
    End With 

    With cl.Interior 
    If bMatch Then 
     .ColorIndex = 0 
    Else 
     .Color = vbYellow 
    End If 
    End With 
    bMatch = False 
Next cl 

End Sub

，另一个用于检查，如果两个长材之间插入值：

Sub ValueToCheck(rng As Range, minV As Long, maxV As Long) 
Dim cl As Range 
Dim bOk As Boolean 

For Each cl In rng.Cells 
    With cl 
    If IsNumeric(.Value) Then 

     If .Value < minV Or .Value > maxV Then 
      .Interior.Color = vbYellow 
     Else 
      .Interior.ColorIndex = 0 
     End If 
    Else 
     .Interior.Color = vbYellow 
    End If 
    End With 
Next cl 
End Sub

然后，一个在小宏使用验证时应使用以下表格：

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim col As Range 
Dim colAdr As String 

For Each col In Target.Columns 
    colAdr = col.Address(ReferenceStyle:=xlR1C1) 
    Select Case Right(colAdr, Len(colAdr) - InStrRev(colAdr, "C")) 
     Case Is = 1 
      ListToCheck col 
     Case Is = 2 
      ValueToCheck col, 1000000, 9999999 
     End Select 
Next col 
End Sub

我假定第一列是要检查一下列表，第二个应该在1000000和9999999之间。但是你可以相应地修改它。正如你所看到的，我不使用excel验证 - 这可能会在粘贴时被用户无意中覆盖。我已经用宏来填充非有效的单元格，但是您可以命令它执行其他操作。如果您认为有人可能会尝试粘贴1 000或更多的值，我不会推荐msgbox。

数据验证如何在Excel中复制/粘贴不同来源的数据时使用

相关推荐