Hi guys,
is it possible, if I have three combobox, and I want to fill them up with the same values, to write a loop to do so?
example:
for i=1 to 3
i=i+1
combobox (i) = .....
.
.
.
loop
Hi guys,
is it possible, if I have three combobox, and I want to fill them up with the same values, to write a loop to do so?
example:
for i=1 to 3
i=i+1
combobox (i) = .....
.
.
.
loop
Assuming your comboboxes are called Combobox1, Combobox2 and Combobox3, you can use:
[VBA] for i=1 to 3
Me.Controls("Combobox" & i) = "whatever"
Next i
[/VBA]
Regards,
Rory
Microsoft MVP - Excel
If your comboboxes are random names then
[VBA]
Dim ctlLoop As msforms.Control
For Each ctlLoop In Me.Controls
If TypeOf ctlLoop Is msforms.ComboBox Then ctlLoop.Value = "YrValue"
Next ctlLoop
[/VBA]
I have tried that, and it worked for only one item. thanksOriginally Posted by rory
how can I do the same thing but with multiple item? (I want to be able to add more items)
I tried to add the lines inside the loop, but it is only recording the last item only.
....
Originally Posted by Reafidy
I have also tried the code from Reafidy and it gave me the same result.Originally Posted by Reafidy
Sorry, I dont understand, can you better explain what the problem is please?
If your data is in an array, this should work.
If the list for the first combo box is already in place, try this,UserForm1.ComboBox1.List = dataArray UserForm1.ComboBox2.List = dataArray UserForm1.ComboBox3.List = dataArray
With UserForm1 .ComboBox2.Clear .ComboBox3.Clear For i = 0 To .ComboBox1.ListCount - 1 .ComboBox2.AddItem .ComboBox1.List(i) .ComboBox3.AddItem .ComboBox1.List(i) Next i End With
Sorry if I am not able to deliver the message the right way.Originally Posted by Reafidy
let's see,
I have a form, in which I created three combobox: combobox1, combobox2, combobox3.
I know how to write the code to additem to a single combobox.
what I am trying to do is if I have 100 comboxbox, I don't want to write the same code for each combobox.
I am trying to loop that, so I can save time.
example:
combobox1 will have in it: item1, item2, item3, item4
combobox2 the same
combobox3 as well.
I hope my description is efficient.
N.B: I am new to VBA.
It workedOriginally Posted by mikerickson
Thanks!!!!
Is it possible to do that without the first combobox already in place?
Im pretty sure the code rory and I have given you will do that.
For example if you have three comboboxes on a userform then run this:
[vba]Private Sub CommandButton1_Click()
Dim ctlLoop As msforms.Control
For Each ctlLoop In Me.Controls
If TypeOf ctlLoop Is msforms.ComboBox Then
ctlLoop.List = Array("Item1", "Item2", "Item3")
End If
Next ctlLoop
End Sub[/vba]
All comboboxs will have them 3 items availble from the dropdown.
You can add as many comboboxes as you like and the code will fill them all with the items.
You can add as many items to the comboboxes as you want by changing the array
YEP it worked, Awesome. Thanks a lot.Originally Posted by Reafidy
How would you modify rory's code to do the same thing?
Rorys code (modified):
[VBA] Dim iLoop As Integer
For iLoop = 1 To 3
Me.Controls("Combobox" & iLoop).List = Array("Item1", "Item2", "Item3")
Next iLoop[/VBA]
Awesome Guys, thank you so much for your help, and for your patience.