Q: How to use a collection to provide options for a drop down list in a user form - Excel VBA 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:

*

*I don't manage to create this "unique values list"

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

A: OK, I'll give it a go.
Sub OSizeBox_Click()
    Dim arr As New Collection, a
    Dim rng As Range
    Dim LRow As Long

    LRow = Worksheets("Sheet_Name").Cells(Rows.Count, "H").End(xlUp).Row

    Set rng = Worksheets("Sheet_Name").Range("H3:H" & LRow)

    For Each a In rng
        ' Debug.Print a
        arr.Add a
    Next

End Sub

That should compile, hopefully it will give you your result. Just a few notes. A range object, and a collection object, need not be defined as arrays, you need to specify what column LRow comes from, from your range I've assumed 'H', and objects should be Set. Make sure, "Sheet_Name", is the name of the sheet your dropdown data is on.
Hope this give you a start.
Edit: I should have also stated I'm using Excel from Microsoft 365, latest version and updaes.
Regards,
njc

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


A: This line
Dim arr() As New Collection

Tells VBA to create a dynamic array of New Collection.  If you look in your locals window you will see that after the above line has executed you get  'arr = Collection()'.
I suspect that this is not what you intended but rather wanted
'Arr=collection'
In which case you should use
Dim Arr as Collection
set Arr = new collection

The form 'Dim ....as New Object' is allowed by VBA but is not a good programming practise.
If you use the above definition of Arr you should find that your 'Arr.add a' should work fine.
You can get lots of feedback on syntax errors if you install the free and fantastic Rubberduck addin for VBA and look at the code inspections it generates.  Be prepared to sigh when you see how many inspection comments your code generates.
