Putting data into combo boxes - is there a quicker way than this?

[Glove303]

I tend to populate combo boxes with loops, which I know can be inefficient.

An example would be this:

For X = 1 To 31
Me.Example_Combo.AddItem Format(X, "00")
Next X

I also add ranges from a worksheet, such as:

For X = 3 To LastRow
Me.Example_Combo.AddItem Data.Cells(X, 1).Value
Next X


I have a feeling I could use arrays to speed things up, but I have searched and cannot find a good answer. Can anyone help me on this please?

Many thanks,
James.

 

[Gary McMaster]

You can pass arrays to a combo box.

http://www.mrexcel.com/forum/showthread.php?t=536014

Gary

 

[RoryA]

You can for example replace this:

For X = 3 To LastRow
Me.Example_Combo.AddItem Data.Cells(X, 1).Value
Next X

with this:

Me.Example_Combo.List = Range("A3:A" & lastrow).Value

 

[RoryA]

Or for your first example, something like:

   Me.ComboBox1.List = Array("01", "02", "03", "04") ' etc!
   ' or:
   Me.ComboBox1.List = [INDEX(TEXT(ROW(1:31),"00"),0)]

 

[Glove303]

Great, thanks guys.

This is a big help

 

[Glove303]

Or for your first example, something like:

   Me.ComboBox1.List = Array("01", "02", "03", "04") ' etc!
   ' or:
   Me.ComboBox1.List = [INDEX(TEXT(ROW(1:31),"00"),0)]

Rorya, I can't get the second example to work. I am unsure whether it is adding the number 1:31 direct from the code, or whether it is getting them from a worksheet?

Also, if I use the range example

Range("A3:A" & lastrow).Value</pre>
Can I format the whole range as "00"? I am not sure how I do that.

 

[RoryA]

What is the second example doing?

For the range, you can only get a formatted value if you loop and use the Text property of each cell or if you format the range as Text and then enter the values the way you want.

 

[Glove303]

What is the second example doing?

For the range, you can only get a formatted value if you loop and use the Text property of each cell or if you format the range as Text and then enter the values the way you want.

When I try it I get "Could not set the list property. Invalid property array index"

 

[RoryA]

Try this for the second one and tell me what it says/does:

   Dim varData
   varData = [INDEX(TEXT(ROW(1:31),"00"),0)]
   If IsArray(varData) Then
      Me.ComboBox1.List = varData
   Else
      MsgBox "Not an array"
   End If

 

[Glove303]

"Not an array"