Read comboboxes into array

[Steph]

Hi. I have a form with 15 comboboxes. I populated the combo box list with
the following code:

Userform1.ComboBox1.List =
Price.Range("LMU").Resize(Price.Range("LMU").Rows.Count - 1).Value

I copied the above code 15 times, because the list for each box is exactly
the same. Can I read the comboboxes into an array, and then populate the
aray with a single statement as above?

Thanks!

 

[Rog]

Steph, this code assumes you only have 15 combos on your
form - if you have more, you should put those 15 in a
frame and loop through the frame's controls rather than
the forms

Rgds

Rog


Dim ctl As Control

For Each ctl In me.Controls
If TypeName(ctl) = "ComboBox" Then
ctl.List =Price.Range("LMU").Resize(Price.Range
("LMU").Rows.Count - 1).Value
End If
Next

 

[Steph]

Hi Rog,

Thanks for the response. My original post was an incredibly simplified view
of my form! I have hundreds of comboboxes and text boxes on multipage tabs.
Your looping code will work great, but would mean I'd have to draw frames
arounds tons of stuff.

Can it be done without frames? can your first line of code be
For Each ctl in Array1 instead of
For Each ctl in me.Controls?

Thanks a bunch!

 

[Rog]

Yes, the loop can certainly be changed; it just means you
will have to populate your own array/collection
beforehand, eg

Dim col_15Combos As New Collection
Dim ctl As Control

col_15Combos.Add ComboBox1
col_15Combos.Add ComboBox2
....
col_15Combos.Add ComboBox15

For Each ctl In col_15Combos
ctl.List = your range

Next