Combobox in VBA user form filled by named range

[Sweedler]

Hello you wonderful EXCEL geniuses

So I have been trying all day to understand how to populate my VBA Userform Combobox. But I just can't figure it out.

Basically I would like to use a named ranges to stand for the contents of my combobox. But I just can't seem to make heads or tails of it right now.

Please help



PS. I would also like to make a secondary USERFORM where there are multiple comboboxes, where the contents are still named ranges, but the named range changes based on the value in the first combobox.

Bonus points for solving this one

 

[My Aswer Is This]

Try this:
Assuming your named Range is named "Alpha"
Modify to your needs.
Put this script in your Userform:

Private Sub UserForm_Initialize()
'Modified  2/3/2022  9:22:13 AM  EST
ComboBox1.List = Range("Alpha").Value
End Sub

 

[My Aswer Is This]

To answer the Bonus question:
This script assumes you have named ranges Named:
"Alpha"
"Bravo"
"Charlie"

Modify and add more as needed:
I load named Ranges Names in Combobox2

And then script in Combobox2 loads named ranges Range value into combox3

Private Sub ComboBox2_Change()
'Modified  2/3/2022  10:03:34 AM  EST
ComboBox3.Clear
ComboBox3.List = Range(ComboBox2).Value
End Sub
Private Sub UserForm_Initialize()
'Modified  2/3/2022  10:03:34 AM  EST
ComboBox1.List = Range("Alpha").Value
With ComboBox2
    .Clear
    .AddItem "Alpha"
    .AddItem "Bravo"
    .AddItem "Charlie"
End With
End Sub

 

[Sweedler]

Try this:
Assuming your named Range is named "Alpha"
Modify to your needs.
Put this script in your Userform:

Private Sub UserForm_Initialize()
'Modified  2/3/2022  9:22:13 AM  EST
ComboBox1.List = Range("Alpha").Value
End Sub

I think I love you, so what am I so afraid of .....

Sweet bit of code man, that solved my problems right there.

 

[Sweedler]

Try this:
Assuming your named Range is named "Alpha"
Modify to your needs.
Put this script in your Userform:

Private Sub UserForm_Initialize()
'Modified  2/3/2022  9:22:13 AM  EST
ComboBox1.List = Range("Alpha").Value
[QUOTE="Sweedler, post: 5832129, member: 468517"]
I think I love you, so what am I so afraid of .....

Sweet bit of code man, that solved my problems right there.
[/QUOTE]
[QUOTE="My Aswer Is This, post: 5828519, member: 292216"]
To answer the Bonus question:
This script assumes you have named ranges Named:
"Alpha"
"Bravo"
"Charlie"

Modify and add more as needed:
I load named Ranges Names in Combobox2

And then script in Combobox2 loads named ranges Range value into combox3

[CODE=vba]
Private Sub ComboBox2_Change()
'Modified  2/3/2022  10:03:34 AM  EST
ComboBox3.Clear
ComboBox3.List = Range(ComboBox2).Value
End Sub
Private Sub UserForm_Initialize()
'Modified  2/3/2022  10:03:34 AM  EST
ComboBox1.List = Range("Alpha").Value
With ComboBox2
    .Clear
    .AddItem "Alpha"
    .AddItem "Bravo"
    .AddItem "Charlie"
End With
End Sub

What would one do if there are more than 3 named ranges .. some of the comboboxes will be sorting through over 100 options.
[/QUOTE]

 

[My Aswer Is This]

I would like to know why you thought you needed named ranges.
Why not use Range("A1:A" & Lastrow) for example
This would mean all the values in column A of the active sheet

And you could have numerous ones:

But if named ranges are more the 3 just add their name to my script:

Not really sure what your overall object is:
I believe you just mentioned what you wanted but not suggestions on what your overall object is and how to do it Making 100 named ranges would require you to make more ranges and if the ranges expanded over time you would have to modify the Range of each named range

 

[My Aswer Is This]

If you have 100 or more named ranges how will the user really know what named range he want to use. What happens when he selects the Name in combobox 1 name "Alpha for example

All I know is you want to put a large number of named ranges in a combobox
I have no ideal what you want to happen when the user clicks on a Range Named name "Alpha" for example.

If I knew the whole goal I maybe to give you other ideal on how to do this.