event change sub

[LFKim2018]

pls help in the code:
to detect a duplicate entry on column C only
many many thanks

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C:C")) Is Nothing Then  
         ** when an entry to column C of a duplicate (using countif(c:c,c&i)>1 formula?)
        MsgBox "No Duplicate allowed!",vbcritical
        Exit Sub
    End If
End Sub

 

[vcoolio]

Hello LFKim,

You could try this:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If Application.WorksheetFunction.CountIf(Columns(3), Target.Value) > 1 Then
MsgBox "No duplicates allowed!", vbCritical, "WARNING"

End If

End Sub

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

..................or this if you need the just entered duplicate cleared:-

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns(3)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

If Application.WorksheetFunction.CountIf(Columns(3), Target.Value) > 1 Then
      If MsgBox("No duplicates allowed!", vbCritical, "WARNING") = vbOK Then
      [COLOR=#ff0000]Target.Clear[/COLOR]
      End If
End If

End Sub

Cheerio,
vcoolio.

 

[LFKim2018]

Mr. vcoolio
the code worked perfectly!
many many thanks

 

[vcoolio]

You're welcome LFKim.
I'm glad that I was able to help.

Cheerio,
vcoolio.

 

[Peter_SSs]

I'm not sure if it is likely or possible with your set-up, but I would allow for the case where multiple cells are changed at once (eg entering with Ctrl+Enter or Copy/Paste)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  Dim sClr As String
  
  Set Changed = Intersect(Target, Columns("C"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If WorksheetFunction.CountIf(c.EntireColumn, c.Value) > 1 Then
        sClr = sClr & ", " & c.Address(0, 0) & " (" & c.Value & ")"
        c.ClearContents
      End If
    Next c
    If Len(sClr) Then MsgBox "Duplicales not allowed. The following cells (values) have been cleared:" & vbLf & Mid(sClr, 3)
    Application.EnableEvents = True
  End If
End Sub

 

[LFKim2018]

Mr. Peter_SSs
Thank you for your reply.
this is even better! checking for copy pasted entries!
Actually this is an offshoot of my query about Data Validation - where it only checks duplicates for typed-in entries and not copy pasted entries.
the only drawback is when copying and the original cell is included - it's location is moved..

many many thanks

 

[LFKim2018]

Mr. Peter_SSs
to elaborate further..
normally when we copy a cell - we shift to the next cell then paste it - your code took good care of that.
but sometimes - when we paste we include the original cell and paste on... the drawback I am speaking of is that your code move the original cell to the last highlighted portion.
if you could refine the code - the better.
many many thanks

 

[Jaafar Tribak]

Mr. Peter_SSs
to elaborate further..
normally when we copy a cell - we shift to the next cell then paste it - your code took good care of that.
but sometimes - when we paste we include the original cell and paste on... the drawback I am speaking of is that your code move the original cell to the last highlighted portion.
if you could refine the code - the better.
many many thanks

I have just seen this thread which I see is related to this thread

The answer I posted in that thread doesn't move the original cell to the last highligted portion BUT it assumes you also have Data Validation set in the range A1:F10 with the formula =COUNTIF($A$1:$F$10,A1) .. Obviously, you can change the DV Range as required.

 

[LFKim2018]

Mr. Peter_SSs
in line with this thread, is there a way to prevent copy paste just for column C only?
meaning disabling Ctrl-C and Ctrl-V - just for column C..
many thanks