I have a column on a worksheet. In this column people will be entering a
number. I was wondering how I would go about disallowing someone to
enter a number that allready exists in the column, and to pop up a
message or something when they try.


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497975

Give this a look...

http://www.cpearson.com/excel/NoDupEntry.htm
--
HTH...

Jim Thomlinson


"cbrd" wrote:


I have a column on a worksheet. In this column people will be entering a
number. I was wondering how I would go about disallowing someone to
enter a number that allready exists in the column, and to pop up a
message or something when they try.


--
cbrd
------------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...o&userid=30009
View this thread: http://www.excelforum.com/showthread...hreadid=497975

hi cbrd

Pu this in code module for the workbook.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Variant
Dim a As Variant
a = Target.Value
Application.EnableEvents = False
' you can change wks number and columns her.
With Worksheets(1).Columns("A:A")
Set c = .Find(a, LookIn:=xlValues)
If c 0 Then
MsgBox "Number allready exists in the column" & Chr(10) _
& "Enter anoder number."
Target = ""
End If

End With
Application.EnableEvents = True
End Sub

Regards Yngve

Thanks for the help. I ended up figuring out a macro to do this. Tha
data validation feature didnt seem to function right. I'd type in 9
knowing it didnt exist in the column, and it would still say i
existed.
I ended up using the following code, but it changes the cell to red, I
rather have it empty the cell and put my cursor in the cell afte
displaying msg.


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim LLoop As Integer
Dim LTestLoop As Integer

Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String

Lrows = 2200
LLoop = 2

While LLoop <= Lrows
LChangedValue = "A" & CStr(LLoop)

If Not Intersect(Range(LChangedValue), Target) Is Nothing Then
If Len(Range(LChangedValue).Value) 0 Then
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop < LTestLoop Then
LTestValue = "A" & CStr(LTestLoop)

If Range(LChangedValue).Value = Range(LTestValue).Value Then
Range(LChangedValue).Interior.ColorIndex = 3
MsgBox Range(LChangedValue).Value & " already exists in cell A" & LTestLoop
Exit Sub
Else
Range(LChangedValue).Interior.ColorIndex = xlNone
End If

End If

LTestLoop = LTestLoop + 1
Wend

End If
End If

LLoop = LLoop + 1
Wend

End Su
-------------------

--
cbr
-----------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...fo&userid=3000
View this thread: http://www.excelforum.com/showthread.php?threadid=49797

hi cbrd

I did not tet i proprly
it should bin lik this
Private Sub Worksheet_Change(ByVal Target As Range)

Dim c As Variant
Dim a As Variant
a = Target.Value
Application.EnableEvents = False

With Worksheets(1).Range("a:a")
Set c = .Find(a, LookIn:=xlValues)
Dim firstaddress As Variant
If Not c Is Nothing Then
firstaddress = c.Address
Do
If c.Address < firstaddress Then
MsgBox "Number allready exists in the column" & Chr(10) _
& "Enter anoder number."
Target = ""
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If


End With

Application.EnableEvents = True
End Sub

regards
yngve

Thanks for the help. It erases the duplicate number I type in, but i
places the cursor down one row in the column. It should work fine fo
my needs though.

Again, Thankyou for your help

--
cbr
-----------------------------------------------------------------------
cbrd's Profile: http://www.excelforum.com/member.php...fo&userid=3000
View this thread: http://www.excelforum.com/showthread.php?threadid=49797