Preventing Duplicates In Data Entry vba in all cells

L

Luthius

Active Member
Joined
Apr 5, 2011
Messages
324
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
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?
 
Upvote 0
(...)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").
 
Upvote 0
Place this in the ThisWorkbook module:
Code: 
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VLOOKUP or similar to cut/paste data
Replies
3
Views
413
kvsrinivasamurthy
K
G
  • Question Question
Cannot create a new time-in entry
Replies
2
Views
184
genesix
G
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
224
Peter_SSs
Peter_SSs
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
125
BenW71
B
W
  • Question Question
Excel VBA - Check For Duplicates and Return the Count on a Large Data Set
Replies
4
Views
360
Kaper
Kaper

Forum statistics

Threads
1,224,599
Messages
6,179,831
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top