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.

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.

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.



.

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.



.

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.



.



.

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?