Loading a two column combo box

[RWWAMS]

Hi everyone,

I using Excel 2000. All I want to do is load a two column combo box. I thought this would be pretty simple, but I'm having quite a bit of trouble. The values that I'm trying to get into the combobox are in an array.

Thanks in advance for any feedback.

 

[RonLeenders]

How to do this is actually documented in the on-line VBA help for Excel (look for examples of loading combo boxes).

- You have the data in an array - good.
- Make sure the array has the same number of columns as your combo box definition, e.g., DIM sArr (1,4) as String for a 2 column x 5 row array called sARR.
- Either via the forms designer properties window or directly in code, set the combobox column count to 2 (in this example)
- Set the "bound column" property to the column that has the value you want the combobox to return when a list row is selected.
- Set the combobox to the array, e.g.,
Set cmbX = sArr

Should be that simple! I wrote this from memory so please check out on-line help if it goes wrong...
Regards
Ron

 

[pvw]

Hi

Here's an example for a combobox with 2 columns and 4 list items (rows):

Dim astrItems(3, 1) As String '(rows/columns)
astrItems(0, 0) = "R1C1"
astrItems(0, 1) = "R1C2"
astrItems(1, 0) = "R2C1"
astrItems(1, 1) = "R2C2"
astrItems(2, 0) = "R3C1"
astrItems(2, 1) = "R3C2"
astrItems(3, 0) = "R4C1"
astrItems(3, 1) = "R4C2"
ComboBox1.List() = astrItems()

If the dimensions of your array are columns/rows (and not rows/columns), use
ComboBox1.Column() = astrItems()
to set the array to the combobox.

I prefer one-based arrays to fill combo- and listboxes. In my opinion the code is easier to read. So my prefered solution is:

Option Base 1
Dim astrItems(4, 2) As String '(rows/columns)
astrItems(1, 1) = "R1C1"
astrItems(1, 2) = "R1C2"
astrItems(2, 1) = "R2C1"
astrItems(2, 2) = "R2C2"
astrItems(3, 1) = "R3C1"
astrItems(3, 2) = "R3C2"
astrItems(4, 1) = "R4C1"
astrItems(4, 2) = "R4C2"
ComboBox1.List() = astrItems()

HTH
Philipp von Wartburg