CODEWITHSUNDEEP
excelcomboboxvba
PEagle
PEagle

Reputation: 35

Populate combobox with unique values without scripting.dictionnary

So far I found a way to populate my combobox but it does not work (Error 429) when it tries to create the object. here is the code I found :

Dim v, e
c = 2
ld = 8
lf = 128
With Sheets("2017-Sem1").Range(Cells(ld, c), Cells(lf, c))
    v = .Value
End With
With CreateObject("scripting.dictionnary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With
End Sub

First of all, I do not undersand exactly what the code is doing, I'll be grateful if you could enlight me. Second it seems that "scripting.dictionnary" does not exist on my computer: I tried

dim dict as scripting.Dictionnary

and it returns a compilation error at once.

So, to my understanding so far, I will not be able to use it (absent DLL, and I cannot go into the folders at my work). Does anyone have an alternative solution that I can use ?

Thank you, PEagle

Upvotes: 1

Views: 3990

Answers (1)

CallumDA
CallumDA

Reputation: 12113

You can do this slightly differently - you don't necessarily need a Dictionary (or Collection). Just use the Combobox.List object. This VBA code takes values in range A1:A10 and only adds to your combobox if it's unique. The code is designed to go in your UserForm Module

Sub Userform_Initialize()
    Dim rng As Range, r As Range
    Set rng = Sheet1.Range("A1:A10")

    For Each r In rng
        AddUnique r.value
    Next r
End Sub

Sub AddUnique(value As Variant)
    Dim i As Integer
    Dim inList As Boolean

    inList = False
    With Me.ComboBox1
        For i = 0 To Me.ComboBox1.ListCount - 1
            If Me.ComboBox1.List(i) = value Then
                inList = True
                Exit For
            End If
        Next i

        If Not inList Then
            .AddItem value
        End If
    End With
End Sub

I tested the code and it works nicely for me. Let me know if you have any troubles

Upvotes: 2

Related Questions