Duplicates in lists

G

Gizmo

I have a user form that has a cell for entering name. I want to be able to
enter data using a dropdown or type in a new value that gets added to the
dropdown. I do not want to see duplicates in the dropdown.
 
D

Dave Peterson

Maybe you could keep track of the list of names in a range on a hidden sheet.
Then you could use application.match() to see if the proposed name already
exists.
 
G

Greg Wilson

Assuming NameList is a named range a containing a header followed by the
names. The first item is not added to the combobox list because it is the
header:-

Private Sub UserForm_Activate()
Dim i As Integer
With Range("NameList")
For i = 2 To .Count
ComboBox1.AddItem .Item(i).Value
Next
End With
End Sub

Private Sub ComboBox1_AfterUpdate()
With ComboBox1
If .Text <> "" And NotInList(.Text) Then .AddItem (.Text) _
Else MsgBox "Permission denied. In list already !!!"
End With
End Sub

Function NotInList(nm As String) As Boolean
Dim i As Integer
Dim found As Boolean
With ComboBox1
For i = 0 To .ListCount - 1
If nm = .List(i) Then
found = True
Exit For
End If
Next
End With
NotInList = Not found
End Function

Greg
 
Top