Warning of duplicates

[Jock]

If a user enters a number in column 'D' which has already been used, rather
than simply identifying that the number has been used already, how can I get
a message box to appear asking if they want to continue using that particular
number or not? If they select yes, end sub, if they select no, target.value =
vbnullstring. It is purely a warning.
In this case, dupes are allowed but I want to alert the user that the number
they have entered has already been used therefore eliminating accidental
dupes (hopefully). Can code inform the user that dupe(s) are on row xx?
Thanks,

 

[Mike H]

Jock, try this worksheet code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRow As Long
If Target.Column <> 4 Then Exit Sub
If WorksheetFunction.CountIf(Range("D"), Target.Value) > 1 Then
MyRow = WorksheetFunction.Match(Target.Value, Range("D"), 0)
response = MsgBox("That number has been used on row " _
& MyRow & " Yes to continue NO to cancel", vbYesNo, "Warning")
If response = vbYes Then
Exit Sub
Else
Target.ClearContents
End If
End If
End Sub

Mike

 

[muddan madhu]

U can use data validation

Select the Range A1:A10 and now

Go to Data | Data Validation | setting : Allow - custom |
Formula : =COUNTIF(A$1:A$10,A1)=1 | ok

 

[Per Jessen]

Hi Jock

This event code has to be pasted into the code sheet for your worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Dupes()
Dim SearchAfter As Range
Set isect = Intersect(Target, Columns("D"))
If Not isect Is Nothing Then
Count = WorksheetFunction.CountIf(Columns("D"), Target.Value)
If Count > 1 Then
msg = MsgBox("The number just entered has been used before" _
& vbLf & vlf & "Do you vant to keep this value? ", _
vbYesNo + vbQuestion, "Duplicate")
If msg = vbNo Then
Target.Value = ""
Else
ReDim Dupes(1 To Count - 1)
Set SearchAfter = Target
For r = 1 To Count - 1
Set f = Columns("D").Find(what:=Target.Value, _
after:=SearchAfter, Lookat:=xlWhole, LookIn:=xlValues)
Dupes(r) = f.Row
Set SearchAfter = f
Next
RowString = Dupes(1)
If Count > 2 Then
For r = 2 To Count - 1
RowString = RowString & ", " & Dupes(r)
Next
End If
msg = MsgBox("Dublets are found in row(s): " & _
RowString, vbInformation + vbOKOnly, "Infomation")
End If
End If
End If
End Sub

Regards,
Per

 

[JockW]

Hi Mike,
Can this be changed to sit in a userform textbox2 exit event which fires
when user exits textbox2? The number is now entered in a userform and
transferred to column 'D' (hopefully after your validation code checks) when
command (close) button is clicked.
Thanks again