How to Fill Listbox on Excel User Form with Unique Values from Worksheet

A website visitor has the following interesting query: How can I copy distinct records from an Excel column (data starts at I2) to a listbox on a userform? The worksheet data has duplicate records and I want no duplicates in my listbox. I have also placed a command button on the form. How to code the command button to transfer data from the Listbox to an Excel cell?
One of the finest ways to get unique data into a listbox on a userform from an Excel worksheet is to use the collection object or variable. A Collection can store large amounts of data like an array but you don’t need to allocate memory for this object since Excel does the job. A collection uses methods like Add, Remove, Count, etc. To add an item to a collection you use the following line of code:
Collection.Add item, [key], [before], [after]
Only the item parameter is essential. The key parameter can, however, make an item unique. Now if you make the key value same as the item value you can avoid duplicates. If you add a duplicate key to an item you’ll get an error. By using the line of code:
On Error Resume Next
you can force Excel VBA code to continue. Study the VBA code below to understand how this works:

Private Sub UserForm_Initialize()
Dim myList As Collection
Dim myRange As Range
Dim ws As Worksheet
Dim myVal As Variant
Set ws = ThisWorkbook.Sheets(“Sheet1”)
Set myRange = ws.Range(“I2”, ws.Range(“I2”).End(xlDown))
Set myList = New Collection
On Error Resume Next
For Each myCell In myRange.Cells
myList.Add myCell.Value, CStr(myCell.Value)
Next myCell
On Error GoTo 0
For Each myVal In myList
Me.ListBox1.AddItem myVal
Next myVal
End Sub

The Excel training video explains in detail how you can fill a listbox on an Excel userform with unique data from a worksheet:

Further reading:
Fundamentals of Collections

Leave a Reply

Your email address will not be published. Required fields are marked *