Limit to List, No dups, Clear Row

[novicevbaer]

Hello,

I posted before but I think the responses that I got were great bu
they interfered with other events in the program design. So I wil
re-post my question with more information. Sorry about that and thank
again for the suggestions.

I have a user form list box in which a user selects an item. The ite
then appears in column a and the cost for the item appears in column b
What I would like to do is this:

1) Limit the users to choosing items only on the list
2) Allow no duplicate occurences in column a
2) If the item chosen is a duplicate, both columns a and b will remai
""
3) If the user chooses an item and later decides to delete the ite
from column a the entire row will clear for their convenience

I think because I had originally attempted to do these in a piecemea
fashion one thing was conflicting with the other

Thank you in advance for any suggestions :

 

[anilsolipuram]

Requirements:
1) Limit the users to choosing items only on the list
2) Allow no duplicate occurences in column a
3) If the item chosen is a duplicate, both columns a and b will remai
""
4) If the user chooses an item and later decides to delete the ite
from column a the entire row will clear for their convenience

Nicely explained

I think 1,2,3 are solved with the below code.Is that correct. If no
please copy the exact code, execute and see the result.

So you want only 4th requirement resolved.





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 = ""
Range("b" & Target.Row).Value = ""
End If
End If
Next
End If
End If
End Su

 

[anilsolipuram]

I think the 4 th requirement should be resolved with updated belo
code.


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).Valu
<> "") Then
If (Range("a" & i).Value = Range("a"
Target.Row).Value) Then
MsgBox "entered duplicate value"
Range("a" & Target.Row).Value = ""
Range("b" & Target.Row).Value = ""
End If
End If
Next
Else
If (Range("a" & Target.Row).Value = "") Then
Range("b" & Target.Row).Value = ""
End If
End If
End If
End Su