Populating a ComboBox (ActiveX) using a variable

[WaqasTariq]

I have 100 combo boxes on an excel sheet, they are divided in three sets (1-25, 26-50, 51-100). Each set has the same value within the set, but is different from the other.

Right now I am able to populate the combo boxes using the following code

ActiveSheet.ComboBox1.AddItem "Value1"
ActiveSheet.ComboBox1.AddItem "Value2"
ActiveSheet.ComboBox1.AddItem "Value3"

^ This is time consuming and error prone. Is there a way I can use a variable to populate the combo boxes?

I have been able to find the following code, but it does not work.

Dim ComboBox As ObjectFor ComboBoxCalc1 = 1 To 25
    Set ComboBox = ComboBox(ComboBoxCalc1).Select
    ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value1"
    ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value2"
    ActiveSheet.ComboBox(ComboBoxCalc1).AddItem "Value3"
Next

I will appreciate any help!

 

[Kenneth Hobson]

Try List and an array.

ActiveSheet.ComboBox1.List = Array("Item1", "Item2", "Item3")

 

[Norie]

Perhaps.

For I = 1 To 25
    ActiveSheet.OLEObjects("ComboBox" & I).Object.List = Array("Value1", "Value2", "Value3")
Next I

 

[WaqasTariq]

Try List and an array.

ActiveSheet.ComboBox1.List = Array("Item1", "Item2", "Item3")

That is going to be very convenient, thank you!

Though I will still like to be be able to use a variable so I can shorten what I have to write.

 

[WaqasTariq]

Perhaps.

For I = 1 To 25
    ActiveSheet.OLEObjects("ComboBox" & I).Object.List = Array("Value1", "Value2", "Value3")
Next I

That would have been awesome to use, but unfortunately results in an error. Googling for the error, any ideas?

"Run-time error '1004':Unable to get the PLEObjects property of the worksheet class"

 

[Norie]

What are the names of the comboboxes?

Is the worksheet with the comboboxes active?

 

[WaqasTariq]

What are the names of the comboboxes?

Is the worksheet with the comboboxes active?

The name of the combox start with ComboBox1, ComboBox2, ComboBox3, and so on.
Yes, the worksheet with the combo boxes is active.

Never mind, got it to work. In my excitement I was trying to use the wrong variable for the for loop.

EDIT: Unable to find a way to mark this as answered. You guys are awesome, Thank you!