VBA Unique values in Combobox list from Listbox column / field

Question

I am looking to make a Combobox list only the unique values in a Listbox field / column every time the listbox's list changes.

For example column 3 in the listbox contains multiple instances of Apple, Strawberry and Banana. I want the combobox to contain only Apple, Strawberry and Banana once each.

Any elegant ideas?

Answer 1

Removing duplicates from a list can be achieved using a dictionary. For the following code to work, you have to add "Microsoft Scripting Runtime" to your references.

Private Sub ListBox1_Change()
    Dim dict As Scripting.Dictionary
    Set dict = New Scripting.Dictionary
    Dim i As Long
    On Error Resume Next
    For i = 0 To ListBox1.ListCount - 1
        dict.Add Key:=ListBox1.List(i), Item:=0
    Next i
    ComboBox1.List = dict.Keys
End Sub

I haven't had the opportunity to test it, let me know if it works.