In this article, we will create a List Box in userform and load it with values after removing duplicate values.
Raw data which we will insert in the List Box, consists of names. This raw data contains duplicity in defined names.
In this example, we have created a userform which consists of List Box. This List Box will display unique names from the sample data. To activate the userform, click on the submit button.
This userform will return the name selected by the user as output in a message box.
Before adding names in the List Box, we have used collection object to remove duplicate names.
We have performed following steps to remove duplicate entries:-
Added names from the defined range in the Excel sheet to collection object. In collection object, we can't insert duplicate values. So, Collection object throws error on encountering duplicate values. To handle errors, we have used error statement “On Error Resume Next”.
After preparing the collection, add all the items from collection to the array.
Then, insert all the array elements to the List Box.
Please follow below for the code
Option Explicit Sub running() UserForm1.Show End Sub 'Add below code in userform Option Explicit Private Sub CommandButton1_Click() Dim var1 As String Dim i As Integer 'Looping through all the values present in the list box 'Assigning the selected value to variable var1 For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then var1 = ListBox1.List(i) Exit For End If Next 'Unload the userform. Unload Me 'Displaying the selected value MsgBox "You have selected following name in the List Box : " & var1 End Sub Private Sub UserForm_Initialize() Dim MyUniqueList As Variant, i As Long 'Calling UniqueItemList function 'Assigning the range as input parameter MyUniqueList = UniqueItemList(Range("A12:A100"), True) With Me.ListBox1 'Clearing the List Box content .Clear 'Adding values in the List Box For i = 1 To UBound(MyUniqueList) .AddItem MyUniqueList(i) Next i 'Selecting the first item .ListIndex = 0 End With End Sub Private Function UniqueItemList(InputRange As Range, _ HorizontalList As Boolean) As Variant Dim cl As Range, cUnique As New Collection, i As Long 'Declaring a dynamic array Dim uList() As Variant 'Declaring this function as volatile 'Means function will be recalculated whenever calculation occurs in any cell Application.Volatile On Error Resume Next 'Adding items to collection 'Only unique item will be inserted 'Inserting duplicate item will through an error For Each cl In InputRange If cl.Value <> "" Then 'Adding values in collection cUnique.Add cl.Value, CStr(cl.Value) End If Next cl 'Initializing value return by the function UniqueItemList = "" If cUnique.Count > 0 Then 'Resizing the array size ReDim uList(1 To cUnique.Count) 'Inserting values from collection to array For i = 1 To cUnique.Count uList(i) = cUnique(i) Next i UniqueItemList = uList 'Checking the value of HorizontalList 'If value is true then transposing value of UniqueItemList If Not HorizontalList Then UniqueItemList = _ Application.WorksheetFunction.Transpose(UniqueItemList) End If End If On Error GoTo 0 End Function
We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.