Duplicates in lists
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. |
Duplicates in lists
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. Gizmo wrote: 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. -- Dave Peterson |
Duplicates in lists
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 |
All times are GMT +1. The time now is 09:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com