Preventing Duplicates using VBA (code)
 

I have a sheet that has 8 digit (111-1111) telephone
numbers in colume B. I need to programatically prevent
anyone from entering duplicate values. I can do it using
the Data - Valadation and the COUNTIF function, but that
leaves the validation runction open so others can change
or delete it. I really would rather do it in VBA.

Help.

Thanks to all...
Jerry McCutchen

Preventing Duplicates using VBA (code)
 

Tony,

Thank you so much! This works as advertised!

Jerry
-----Original Message-----
Jerry

Put the following into a worksheet code area.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1").EntireColumn) Is
Nothing Then
If WorksheetFunction.CountIf(Range("B:B"), Target) 1
Then
MsgBox "Duplicate numbers not allowed"
Target.ClearContents
End If
End If

End Sub

Tony
-----Original Message-----
I have a sheet that has 8 digit (111-1111) telephone
numbers in colume B. I need to programatically prevent
anyone from entering duplicate values. I can do it
using
the Data - Valadation and the COUNTIF function, but that
leaves the validation runction open so others can change
or delete it. I really would rather do it in VBA.

Help.

Thanks to all...
Jerry McCutchen
.

.