Hi all,

I am working in Excel 2000. I want to use the data/validation functio
but the problem is I have two conditions.

I have column A (A1:A100) that I want the users to be forced to selec
items only from a list so that they cannot type in anything not in th
list. I also want them to be unable to have duplicate entries in th
column. Currently I have used the data/validation to limit them to th
list but I was wondering how I would go about enforcing the additiona
condition that the column would not allow duplicates.

Can anyone please point me in the right direction? Thank you i
advance

--
novicevbae
-----------------------------------------------------------------------
novicevbaer's Profile: http://www.excelforum.com/member.php...fo&userid=1256
View this thread: http://www.excelforum.com/showthread.php?threadid=31935

Go to tools -macro-visual basic editor. double click sheet1, which i
below vba project.

copy the below code and paste it in the editor space.

This code work for row a1:a100

Private Sub Worksheet_Change(ByVal Target As Range)
If ((Target.Row = 1 And Target.Row <= 100) And Target.Column = 1
Then
If (Range("a" & Target.Row).Value < "") Then
For i = 1 To 100
If (i < Target.Row And Range("a" & Target.Row).Value <
"") Then
If (Range("a" & i).Value = Range("a"
Target.Row).Value) Then
MsgBox "entered duplicate value"
Range("a" & Target.Row).Value = ""
End If
End If
Next
End If
End If
End Su

--
anilsolipura
-----------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...fo&userid=1627
View this thread: http://www.excelforum.com/showthread.php?threadid=31935

THANK YOU SO MUCH it works brilliantly!


--
novicevbaer
------------------------------------------------------------------------
novicevbaer's Profile: http://www.excelforum.com/member.php...o&userid=12567
View this thread: http://www.excelforum.com/showthread...hreadid=319355