Preventing Duplicates In Data Entry vba in all cells

[Luthius]

Hey Guys, I need a function that do a loop through all cells in worksheet based in criteria, not allowing data entry of duplicates values. The criteria I'll put in function for example:

Public Function NoToDuplicate(column as interger)
'The loop
...
Msgbox ("This is a duplicate value")
...
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
Call NoToDuplicate(1)
End Sub

For Example:
Everytime when I'll put some value in column (myCriteria - for example "A" column), the function will check if there's the same value in all cells of the column (myCriteria - for example "A" column) of all sheets of my workbook.
All 'A Columns' of all sheets from the same workbook will be checked and if there's duplicate value a msgbox ("This is a duplicate value")

Luthius

 

[GlennUK]

You say "loop through all cells in worksheet" in one place in your post, and then "all sheets from the same workbook will be checked". Which is it to be?

 

[Luthius]

(...)the function will check if there's the same value in all cells of the column (myCriteria - for example "A" column) of all sheets of my workbook.
All 'A Columns' of all sheets from the same workbook will be checked and if there's duplicate value a msgbox ("This is a duplicate value").

 

[Luthius]

Anyone?

 

[Nalani]

Place this in the ThisWorkbook module:

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
 If Target.Column = 1 Then
        If Application.CountIf(Columns(1), Target) > 1 Then
        '//If duplicate found notify user
        intResponse = MsgBox(Prompt:="Value already exists on another sheet", Title:="DUPLICATES NOT ALLOWED")
End If
End If
End Sub

 

[Luthius]

It is not working.
I had tested and the message is showed Just when the value is duplicate in same sheet. I entried in A column of the the sheet1 the values a,b,c,d and the sheet2 in the same column the values a,b,c,d and the message just showed when I repeat one of these values in the same sheet. And the message need to be showed when the value is duplicated in the workbook. The way it is the message just appears when the value is duplicated in the same sheet not in the workbook.

It Is not working.

 

[Luthius]

Anyone can help me with this?

 

[mikerickson]

If you have columns Name, Number, Color and the exiting data

Bob 123 Red
John 456 Blue
Dave 789 Green

Would John 387 Purple be an OK entry (there is no other John 387 Purple)?
Would Sam 864 Blue be OK or does the duplicate Blue make than unacceptable.

If you are just looking to avoid duplicates in one column, vb is not needed. Validation with a formula like =(COUNTIF(A:A, A1)=1) would prevent the user from entering a duplicate entry in column A.

 

[Glory]

1) Iterate through sheets in workbook
2) Iterate through cells in each sheet's usedrange

If Target = theCell Then

Your messagebox.

This would be really slow, I don't understand Nalani's method at all. Maybe some more explanation from that person would be helpful.

Edit: Mike, would that work for multiple sheets?

 

[Luthius]

No I need in VB code.
In my A Colum I'll entry with a code (ID for example) that cannot be duplicated in another sheet from my workbook. I need to find a way to preventing duplicated records in my workbook using as criteria the column specified.