How to give an array value to different comboboxes in vba excel?

[dpa]

Hi, I have 4 comboboxes in a formcontrol (excel 2007, vba), each combobox has the name: Combobox_01 Combobox_02 Combobox_03 Combobox_04 I want to give all comboboxes the same array values "A","B","C","D","E","F" How do I do this efficiently?, I tried a For-NEXT loop, but I am not sure how to assign the For value from 1 to 4 to each combobox and it gives me an error...

 

[stockchaser98664]

although it doesn't need to be in the click event:

Private Sub UserForm_Click()
ary = Array("A", "B", "C", "D", "E", "F")
For i = 1 To 4
  Controls("Combobox_" & Format(i, "00")).List = ary
Next i
End Sub

This seems to only work if one is using a userform. I have an activex combobox in a worksheet. This does not work with that. How would the coding change to work with an activex combobox embedded in a worksheet?

 

[RoryA]

You would need to refer to the OLEObjects collection:

Me.OLEObjects("Combobox_" & Format(i, "00")).Object.List = ary

If the code is not in the same worksheet, use the relevant sheet rather than 'Me'.

 

[stockchaser98664]

When I add that in, it comes back with a compiler error.

Run time error 1004

Application-defined or object-defined error

 

[RoryA]

Please post all of your code and state where the code is located - i.e. which module - and which line causes the error.

 

[stockchaser98664]

Nevermind. It was the Format(I, "00").

It was making the combobox name "ComboBox01" and my boxes were named "ComboBox1"

Thank you for the help!