macros to prevent duplicates

[grizz]

I have copied this macro and applied it to my sheet but I only want it to prevent duplicates in column C
not being well versed in macros I'm not sure what to change

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) 
If IsEmpty(Target.Value) Then Exit Sub 
If Target.Count > 1 Then Exit Sub 
If Target.Column < 2 Then Exit Sub 
With Range(Cells(1, Target.Column).Address & ":" & Cells(Target.Row - 1, Target.Column).Address & "," & Cells(Target.Row + 1, Target.Column).Address & ":" & Cells(Rows.Count, Target.Column).Address) 
Set c = .Find(Target.Value, , , xlWhole) 
If Not c Is Nothing Then 
MsgBox "P.O.# has been used: " & c.Address(0, 0) 
Target.Value = "" 
End If 
End With 
End Sub

this code prevents duplicates in all columns

 

[Tom Urtis]

The first line of your code should be

If Target.column <> 3 then exit sub

However, you said you applied it to your sheet but what you really did, if you placed this code where it was meant to go, was to apply it to all worksheets in your workbook by placing the code in your workbook module.

 

[grizz]

Thanks Tom Urtis that works excellent and you are correct I did put it in the workbook

 

[grizz]

I am having trouble with the code --- if I enter duplicate numbers in column it says PO # has been used but then will not x-out and freezes workbook

 

[Peter_SSs]

Two things I note about the code (with Tom's suggestion included)

1. Because of the line
If Target.Count > 1 Then Exit Sub
and/or the line
If Target.Column <> 3 Then Exit Sub
It is not robust in preventing duplicates. To demonstrate,

a) Put the same PO# in, say, Z1 and Z2 and then copy Z1:Z2 and paste into column C

b) Select B10:C11, enter a PO# and confirm with Ctrl+Enter, not just Enter

Of course if you are sure your user(s) are never going to do something like that then it won't be a problem.

2. The line
Target.Value = ""
will unnecessarily trigger this code again, so it is good practice to disable events before making such changes and re-enable events afterwards.

If you wanted to address the above issues, and assuming that the user will be just entering/pasting numbers and/or text in col C, this should cover most scenarios I think.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Cchanged <SPAN style="color:#00007F">As</SPAN> Range, Cel <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> temp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Cchanged = Intersect(Target, Columns("C"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Cchanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cel <SPAN style="color:#00007F">In</SPAN> Cchanged<br>            temp = Cel.Value<br>            <SPAN style="color:#00007F">If</SPAN> Len(temp) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                Cel.ClearContents<br>                <SPAN style="color:#00007F">Set</SPAN> c = Columns("C").Find(What:=temp, LookAt:=xlWhole)<br>                <SPAN style="color:#00007F">If</SPAN> c <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    Cel.Value = temp<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    MsgBox "P.O.# '" & temp & "' has been used: " & c.Address(0, 0)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Cel<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[grizz]

much better
thanks Peter

 

[Peter_SSs]

Just looking at this again, clearing the 'Cel' to do the search and then replacing the value if necessary seemed like a good idea at the time but wasn't needed. This makes a little more sense to me now.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> Cchanged <SPAN style="color:#00007F">As</SPAN> Range, Cel <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> temp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Cchanged = Intersect(Target, Columns("C"))<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Cchanged <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cel <SPAN style="color:#00007F">In</SPAN> Cchanged<br>            temp = Cel.Value<br>            <SPAN style="color:#00007F">If</SPAN> Len(temp) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> c = Columns("C").Find(What:=temp, _<br>                    After:=Cel, LookAt:=xlWhole)<br>                <SPAN style="color:#00007F">If</SPAN> c.Address <> Cel.Address <SPAN style="color:#00007F">Then</SPAN><br>                    Cel.ClearContents<br>                    MsgBox "P.O.#: " & temp & vbLf & _<br>                        "has been used: " & c.Address(0, 0)<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> Cel<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>