Preventing Duplicate Entries Across Sheets
 

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

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.

Preventing Duplicate Entries Across Sheets
 

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 D." wrote in message
...
I posted this in the misc. category, but figured I might
get better responses he

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.

Preventing Duplicate Entries Across Sheets
 

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.
-----Original Message-----
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 D." wrote in
message
...
I posted this in the misc. category, but figured I might
get better responses he

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.



.

Preventing Duplicate Entries Across Sheets
 

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" wrote in message
...
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.
-----Original Message-----
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 D." wrote in
message
...
I posted this in the misc. category, but figured I might
get better responses he

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.



.

Preventing Duplicate Entries Across Sheets
 

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?
-----Original Message-----
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" wrote in
message
...
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.
-----Original Message-----
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 D." wrote in
message
...
I posted this in the misc. category, but figured I
might
get better responses he

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.



.



.

Preventing Duplicate Entries Across Sheets
 

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" wrote in message
...
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?

Preventing Duplicate Entries Across Sheets
 

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
-----Original Message-----
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" wrote in
message
...
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?



.

Preventing Duplicate Entries Across Sheets
 

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" wrote in message
...
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
-----Original Message-----
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" wrote in
message
...
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?



.

Preventing Duplicate Entries Across Sheets
 

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
-----Original Message-----
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" wrote in
message
...
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
-----Original Message-----
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" wrote in
message
...
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?



.



.

Preventing Duplicate Entries Across Sheets
 

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" wrote in message
...
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
-----Original Message-----
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" wrote in
message
...
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
-----Original Message-----
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" wrote in
message
...
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?



.



.

Preventing Duplicate Entries Across Sheets
 

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
-----Original Message-----
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" wrote in
message
...
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
-----Original Message-----
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" wrote in
message
...
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
-----Original Message-----
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" wrote in
message
...
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?



.



.



.