Excel VBA Multi Column ComboBox

  • Thread starter Thread starter jlclyde
  • Start date Start date
J

jlclyde

I have a combo box on a user form and have the box filling in with
concatenated data and woudl liek to make it into columns instead.
Here is the code that I have. The loop goes through and takes out all
of the "Item Numbers" which are in every 4th row. Then I will also
need the Quantity which is in i.offset(1,0) and the Date in i.offset
(2,0) and finally if the interior color is 4 I want the word
"Promise". I hope you can help.

For Each i In Rng1 'fills in items from run Combo at bottom
from cut area
'Check if it is a promise

If i <> "" And i.Row Mod 4 = 0 Then
If i.Interior.ColorIndex = 4 Then
Prom = "Promise"
Else
Prom = ""
End If
AvailableItems.AddItem (i & " " & i.Offset(1, 0) & "
Cut " & _
i.Offset(2, 0) & " " & Prom)
End If
Next i

Thanks,
Jay
 
HI

You need to do most of the work in VB in the Properties window for the combo
box. First, set ColumnCount property to the number of columns, in your case 4.
now set the ColumnWidths to best fit your data in points e.g. 100,40,55,120.
Now to populate the combobox create an array with the same number of columns
i.e. and then fill the "row" entries where they apply. For example if you
have 12 rows you would define the array as:

Dim MyArray (1 to 12, 1 to 4)

Once the array is filled with the info you need simply use the following:

ComboBox1.List = MyArray

If this helps please click "Yes"
<><><><><><><><><><>
 
How do you do this if it is not a fixed number of rows? I never know
how many items are going to be in a column until the macro starts
countign them. For instance I have a range of items down A starting
from 52 and going to the end of the sheet. I want to list all item
Numbers which will be every 4th row and the rest of the columns are
offset from this number. Hwo woudl I set the Array if I do nto knwo
how far down it goes?

Thanks,
Jay
 
What you do is initialise the array as a dynamic array with the Dim statement
as below. Then run your procedure to count the number of rows. Now use the
"ReDim" statement to set the array size and then fill the array as required.

Sub MySub()
Dim MyArray()

<Your function to count the rows>

ReDim MyArray (1 To NumberofRows, 1 To 4)

<Your function to fill array>

ComboBox1.List = MyArray
End Sub

If this helps please click "Yes"
<><><><><><><><><><>
 
Back
Top