VB to create 2 data validations

[novicevbaer]

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

 

[anilsolipuram]

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

 

[novicevbaer]

THANK YOU SO MUCH it works brilliantly!