Fill a List Box with unique values from a worksheet using VBA in Microsoft Excel

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.

Logic explanation

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:-

1. 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”.

2. After preparing the collection, add all the items from collection to the array.

3. 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

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

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 info@exceltip.com