How to use a collection to provide options for a drop down list in a user form - Excel VBA

Question

I'm trying to have a dropdown list in a user form to be filled with unique values extracted from a list located in the same workbook. I want to avoid to have to print this list of unique values. I have two issues here:

1. I don't manage to create this "unique values list"
2. I don't know how to make my dropdown list to use the unique value list previously generated.

Here is my unsuccessful attempt (I want the collection of unique values to be made out of the values in the H column) :

Private Sub OSizeBox_Click()
Dim arr() As New Collection, a
Dim rng() As Range

Dim LRow As Long

LRow = Cells(Rows.Count, 2).End(xlUp).Row


rng() = Range("H3", "H" & LRow)

For Each a In rng
    arr.Add Str(a), Str(a)
Next


OSizeBox.RowSource = arr()

End sub

While running this code, I get the following error : "Compile error: Invalid qualifier", highlighting the arr in my For/Next loop.

Any help or advice would very much appreciated! Thank you in advance.

Answer 1

Try this code:

Option Explicit

Private Sub UserForm_Activate()  'starts when the form becomes active
    Dim col As New Collection, rng As Range, a As Variant
    
    With ThisWorkbook.Worksheets(1)  'your WB and WS
        Set rng = .Range("H3", .Cells(.Rows.Count, "H").End(xlUp))
    End With
    
    On Error Resume Next    ' error suppression if the key is not unique
    For Each a In rng
        col.Add a.Text, a.Text  'added only unique values
    Next
    On Error GoTo 0 ' disable error handling
    
    For Each a In col
        Me.OSizeBox.AddItem a 'add unique values from col
    Next
End Sub