Data validation not detecting pasted data

[LFKim2018]

in the Data Validation check for duplicates (using countif(range,cell) formula) - it is detecting KEY IN entry
BUT NOT copy pasted data.
Is there a way for Data validation to capture also the copy pasted data?
many thanks

 

[footoo]

Paste values only.

 

[LFKim2018]

Mr. footoo
pardon, I can't get what you mean.
what I am saying is when I put a DATA > Data Validation on a cell for checking duplicate - it only captured the Keyed-in data - but not the copy pasted values. meaning if there is a previous data - I copy and paste it on another cell in the column - there is no duplicate error. It captured the duplicate error only when the duplicate is typed in.
I hope I made it clear..
thanks..

 

[footoo]

I think you need VBA.

 

[LFKim2018]

yes, and Mr. vcoolio was very kind to help.
thank you..

 

[Jaafar Tribak]

yes, and Mr. vcoolio was very kind to help.
thank you..

Try this code in the worksheet module (The code assumes that the Data Validation Range is A1:F10.. Change range address as required)

Option Explicit

Private Const DV_RANGE_ADDRESS As String = "$A$1:$F$10" [B][COLOR=#008000]' <= Change this DV range as required[/COLOR][/B]

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sLastOpration As String, sMessageAlert As String
    Dim sPaste As String, sAutoFill As String, sDragNDrop As String
    
    With Application
        If .LanguageSettings.LanguageID(msoLanguageIDInstall) = 1033 Then ' English
            sPaste = "Past": sAutoFill = "Auto Fill": sDragNDrop = "Drag and Drop"
            sMessageAlert = "Paste, Auto Fill and Drag&Drop operations are not allowed in range :  " & DV_RANGE_ADDRESS
        ElseIf .LanguageSettings.LanguageID(msoLanguageIDInstall) = 1036 Then 'French
            sPaste = "Coll": sAutoFill = "Recopie Incrémentée": sDragNDrop = "Glisser-déplacer"
            sMessageAlert = "Les operations de 'Collage', 'Recopie Incrémentée' et 'Glisser-déplacer' ne sont pas permises dans la plage :  " & DV_RANGE_ADDRESS
        End If
        
        If Not Intersect(Target, Range(DV_RANGE_ADDRESS)) Is Nothing Then
            sLastOpration = .CommandBars.FindControl(ID:=128).Control.List(1)
            If Left(sLastOpration, 4) = sPaste Or sLastOpration = sAutoFill Or sLastOpration = sDragNDrop Then
                .EnableEvents = False
                .Undo
                .EnableEvents = True
                 MsgBox sMessageAlert, vbCritical, "Data Validation."
            End If
        End If
    End With
End Sub