CODEWITHSUNDEEP
excelcomboboxvba
Awill
Awill

Reputation: 117

Multiple ranges to populate single column combobox

I apologize if this comes across as a stupid question but I cannot find an answer anywhere, just 101 ways to create a multi column combobox.

I have 3 named ranges that I want to combine into one list for a combo box.

I do not want to simply combine all of these to one column/range where the ranges are located because I have another userform with a combobox that changes to one of these 3 ranges depending on a previous user selection.

I simply (well apparently not so for me) want one long list with data from each of these named ranges.

whereas my other comboboxes are set on userform initialize with something like:

combobox1.list = Range("NamedRange1").Value

I need a way to get my list to =namedrange1 & namedrange2 & namedrange3

Thanks in advance for any direction.

Upvotes: 0

Views: 2847

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

Try the code below (on your UserForm_Activate or UserForm_Initialize event):

Private Sub UserForm_Activate()

Dim UnionRange          As Range
Dim cell                As Range

' use Union to merge as many named ranges you need
Set UnionRange = Union(Range("NamedRange1"), Range("NamedRange2"), Range("NamedRange3"))

Me.ComboBox1.Clear

For Each cell In UnionRange
    ComboBox1.AddItem cell.Value
Next cell
ComboBox1.ListIndex = 0

End Sub

Upvotes: 2

Chrowno
Chrowno

Reputation: 198

It is, as far as I know, not possible to concatenate named ranges to the .list or .rowsource property. Instead you could loop over the ranges and add its values:

Dim rCell As Range

For Each rCell In Worksheets("YourSheet").Range("namedrange1")

    ComboBox1.AddItem rCell.Value

Next rCell

For Each rCell In Worksheets("YourSheet").Range("namedrange2")

    ComboBox1.AddItem rCell.Value

Next rCell
....

Upvotes: 1

Related Questions