Prevent User Enter Duplicates in Column

[jade82]

I have a spreadsheet that users need to enter part numbers that we have quoted for. I need to prevent users from entering duplicates in column C. Data validation isn't an option since users copy & paste sometimes into that column.

The code works fine if user happens to copy & paste a part number already on that list. But for some reason, sometimes the code will work & sometimes it fails to catch duplicates when users type in the part numbers. Sometimes users will insert a row & type a part number & the code won't catch the duplicate part number.

Need help with the code to catch any duplicates in column C & sometimes the data in column C is non contiguous so the code has to check if the data entered matches any cell in that column. I would also like for the message box to also display the cell location were the original data is located.

Below is the code used & I also attached a sample of my spreadsheet.

Any help is greatly appreciated!

Please Login or Register  to view this content.

[grizzly6969]

you can use data validation and this code will prevent copy/paste

[jade82]

Thanks so much for your quick response. But I really need to be able to use copy & paste in that part column. Because sometimes we get a list of several parts from an email or another spreadsheet & the user needs to be able to copy & paste that part number in column to keep track of parts that have been quoted already & need to be quoted. There are times when this list is just too long to type & it is more efficient to copy & paste. I was hoping to accomplish this with some code. Is it possible to accomplish this with code without using data validation & still be able to use copy & paste.

[MarvinP]

Hi jade82 and welcome to the forum.

How about using Conditional Formatting where a duplicates of anything in a column would get a background color, showing it was a dup? See the attached.

[jade82]

Thanks MarvinP for your wonderful suggestion. However, conditional formatting would be difficult since users fill the spreadsheet with different colors for which ones need to followup, no bid, waiting for more info, & approval. There would just be too much color going on in the spreadsheet & pasting doesn't flag any repeats. I will be sure to keep your conditional formatting tip in my mind for future tasks/ requirements!!!

[MarvinP]

Hi
I think you need a conditional format rule that triggers a macro. I wonder if that's possible?

[MarvinP]

How about using your same code in a Change_Selection event too? That way you would be checking when they gust moved from one cell to another. Seems like that might slow things down.

[grizzly6969]

Try this code thanks to Peter SS

see my next post

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[grizzly6969]

let me try this again --- I thought I had used code tags

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Cchanged As Range, Cel As Range, c As Range
Dim temp As Variant

Set Cchanged = Intersect(Target, Columns("C"))
If Not Cchanged Is Nothing Then
Application.EnableEvents = False
For Each Cel In Cchanged
temp = Cel.Value
If Len(temp) > 0 Then
Cel.ClearContents
Set c = Columns("C").Find(What:=temp, LookAt:=xlWhole)
If c Is Nothing Then
Cel.Value = temp
Else
MsgBox "P.O.# '" & temp & "' has been used: " & c.Address(0, 0)
End If
End If
Next Cel
Application.EnableEvents = True
End If
End Sub

[royUK]

You had attempted to use Quote tags.

In future please edit the original post

[MarvinP]

See the attached where both OnChange and OnSelect are triggered.

See if this is what you need.

ALSO - it seems you can create a function and put it in a conditional formating criteria. I wonder if this would be another way to accoumplish your goal. Read http://www.bettersolutions.com/excel...I928110883.htm all pages (click next on bottom of each page). I found this very interesting but couldn't make it solve your problem.

[jade82]

I have been busy these past few days trying to figure how to accomplish my user's request. There has been a lot of testing!!!

MarvinP's suggestion didn't catch duplicates that were typed into the column & it would pop up the error message box twice in a row.

I ended up using grizzly6969's code. I only changed the Sub Workbook_SheetChange to worksheet_change. It always catches any duplicates that are copy & paste above or below the part # already in the column, which is great. For some reason, it doesn't always pop up an error message when a user types a part # in the column.

Does anyone have any suggestions of how to modify the code below to always catch any duplicates typed into that column above or below if that part # already exists somewhere in the column?

Any help is greatly appreciated!! Thanks in advance!!

Please Login or Register  to view this content.