Preventing Duplicate Entries Across Sheets

[Ken D.]

I posted this in the misc. category, but figured I might
get better responses here:

Anyone know how to prevent duplicate entries of numbers?
e.g. I have two sheets. I want to make sure that the same
number is not entered more than once. Thank you.

 

[Bernie Deitrick]

Ken,

You can use the workbook's worksheet change event: copy the code below and
paste it into the ThisWorkbook object's codemodule

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Dim mySh As Worksheet
Dim mySum As Integer
mySum = 0
For Each mySh In ThisWorkbook.Worksheets
mySum = mySum + Application.CountIf(mySh.Cells, Target.Value)
Next mySh
If mySum > 1 Then
MsgBox "Hey, Ken, that's already used!"
With Application
.EnableEvents = False
.Undo
MsgBox "And don't do that again...."
.EnableEvents = True
End With
End If
End Sub

 

[Ken]

Thank you very much. This seemed to work pretty well. Do
you know a way to limit it to a specific column? e.g.
Column A is full of unique numbers while other columns may
repeat info. Also, how difficult is it to create a way to
allow user to override and enter a duplicate in unforeseen
circumstances? I know I'm asking a bunch of questions, but
any help would be great. Thanks again.

 

[Bernie Deitrick]

Ken,

This version will limit the checking to column A (both for entry and for
duplicate checking - wasn't sure if you wanted to disallow duplicates of
values in other columns, or just of column A) and allow a user to override
it to enter a duplicate. Anyway, try it out, and let me know if this is how
you want it to behave.

HTH,
Bernie
MS Excel MVP

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Dim mySh As Worksheet
Dim mySum As Integer
mySum = 0
For Each mySh In ThisWorkbook.Worksheets
mySum = mySum + Application.CountIf(mySh.Range("A:A"), Target.Value)
Next mySh
If mySum > 1 Then
MsgBox "Hey, Ken, that's already used!"
With Application
.EnableEvents = False
If MsgBox("Do you want to Enter that anyway?", _
vbYesNo) = vbNo Then
.Undo
End If
..EnableEvents = True
End With
End If
End Sub

 

[Ken]

this works great. thanks so much. can i apply it to just
two of three sheets? e.g. sheet 2 and 3, but not sheet 1?
or does that require more VBAing?

 

[Bernie Deitrick]

Ken,

Change
If Target.Cells.Count > 1 Then Exit Sub

To
If Target.Cells.Count > 1 Or (Sh.Name <> "Sheet2" _
Or Sh.Name <> "Sheet3") Then Exit Sub

And change:
For Each mySh In ThisWorkbook.Worksheets

to
For Each mySh In Sheets(Array("Sheet2", "Sheet3"))

HTH,
Bernie
MS Excel MVP

 

[Ken]

Hmmm... for some reason, this substitution doesn't work -
it allows duplicates. Any ideas? I'd love to try and learn
VBA a bit. Any good books/sites? Thanks again!

Ken

 

[Bernie Deitrick]

Ken,

It doesn't work because I'm stoopid and didn't check my code.

Change the OR to an AND in this line:

If Target.Cells.Count > 1 Or (Sh.Name <> "Sheet2" _
Or Sh.Name <> "Sheet3") Then Exit Sub

Should be:

If Target.Cells.Count > 1 Or (Sh.Name <> "Sheet2" _
And Sh.Name <> "Sheet3") Then Exit Sub


Sorry about that,
Bernie
MS Excel MVP

 

[Ken]

Well, i'm doing something wrong. It works great with the
whole workbook but when i tried to make it just those two
sheets, it stops working altogether. However, I don't want
to have to keep bugging you for help, so I guess I'll just
leave it at the workbook level... But thanks so much.

Ken

 

[Bernie Deitrick]

Ken,

Make sure that the spelling and spacing of the Sheet2 and Sheet3 strings in
this line exactly match the spelling and spacing on the sheet tabs:

If Target.Cells.Count > 1 Or (Sh.Name <> "Sheet2" _
And Sh.Name <> "Sheet3") Then Exit Sub

Other than that, it works great for me..... I can send you a working version
if you are interested, and then you can figure out from there where you are
going astray.

HTH,
Bernie
MS Excel MVP

 

[Ken]

Working great now. Looks like i had to change the security
parameters. Thanks again for all your help. You are indeed
an Excel guru.

-Ken