Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read comboboxes into array
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read comboboxes into array
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 -----Original Message----- 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! . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read comboboxes into array
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" wrote in message ... 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 -----Original Message----- 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! . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read comboboxes into array
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 -----Original Message----- 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" wrote in message ... 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 -----Original Message----- 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! . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Read Worksheet Data into VBA Array? | Excel Discussion (Misc queries) | |||
using VB to read in a column of values of varying length into an array | Excel Worksheet Functions | |||
Excel, read in an array | Setting up and Configuration of Excel | |||
Read in Array | Excel Programming | |||
Read Range Data into Array | Excel Programming |