Populating a Listbox from array

[Rocky0201]

Hi,

I have a listbox which I wish to have more than 10 columns (11 to be exact). I am trying to build an array with the items for each column but I am having problems understanding how to build the array, how to redim, etc. The array will have to have variable number of rows of 11 columns for the listbox.

Once I have the array built, I would set the listbox's column count to 11, set the column widths and then do a listbox6.list = varray

Please help...

Thanks,

 

[Norie]

Can I ask a few questions?

Do you want to populate with all the data from listbox5?

Are any of the listboxes/textboxes linked in anyway?

Finally textboxes can only have one value, so how can you populate an entire column from the value in a textbox?

Oh, almost forgot - do listbox5 and listbox8 have the same no of rows and do you want to populate with the first item from listbox8 and the first row from listbox5?

 

[GTO]

Thanks for responding Norie.

The listboxes are on userform1.

I am using all 8 columns from listbox5.

The 11 columns in listbox6 will consist of:

Column 0 = Value of ComboBox8
Columns 1 thru 8 = Columns 0 thru 7 of Listbox5
Column 9 = Value of TextBox8
Column 10 = Value of TextBox9

There will be a variable number of rows in listbox6 (based on the number of rows in listbox5).

Thanks again Norie.

Hi there,

Just as I don't see it mentioned, the bits in red are bound to fail. There is a 10 column limit in a list/combobox (ie 0 thru 9).

 

[Norie]

GTO

I think that limit only applies when you use AddItem to populate the listbox.

If you use an array you can have more than 10 columns, though there probably is a limit.

Mind you whatever the limit having that many columns in a listbox doesn't seem too practical, just think of the width.

 

[GTO]

GTO

I think that limit only applies when you use AddItem to populate the listbox.

If you use an array you can have more than 10 columns, though there probably is a limit.

Mind you whatever the limit having that many columns in a listbox doesn't seem too practical, just think of the width.

......

ACK! A thousand sorries, Norie is quite right of course.

I cannot even tell you how long ago I originally mis-read the help topic, but that tidbit of bad info has been stuck in my head for quite some time! Thank you for the correction Norie.

Mark

 

[Norie]

Mark

I had to check myself.

I already had a listbox set up to take 8 columns, so I expanded the data to 15 columns.

Then I wondered why it wasn't populating with all the data.

Was there a limit or could I have neglected to increase the column count of the listbox?

 

[Rocky0201]

Norie,

If, for instance, .additem supported more than 10 columns, my code would look like the following:

'// Loop thru Each row in ListBox5
            For x = 0 To Me.ListBox5.ListCount - 1
    
                If ComboBox6.Value = Me.ListBox5.List(x, 0) Then
                    ReDim Vt(10)
                    
                    Vt(0) = ComboBox8.Value '// Leg of route
                    Vt(1) = "" '// From
                    Vt(2) = ComboBox6.Value '// To
                    Vt(3) = Me.ListBox5.List(x, 1) '// City
                    Vt(4) = Me.ListBox5.List(x, 2) '// State
                    Vt(5) = Format(Me.ListBox5.List(x, 3), "00000") '// Zip
                    Vt(6) = Me.ListBox5.List(x, 4) '// Day
                    Vt(7) = Format(Me.ListBox5.List(x, 5), "mm/dd/yyyy") '// Date
                    Vt(8) = Format(Me.ListBox5.List(x, 6), "hh:mm AM/PM") '// Time
                    Vt(9) = TextBox8.Value '// Mileage
                    Vt(10) = TextBox9.Value '// Travel ime
                    
                    '// Load ListBox6 with the row
                    With ListBox6
                        .ColumnCount = 11
                        .ColumnWidths = "30;40;40;100;30;35;55;60;55;30;30"
                        .AddItem Vt(0)
                        .List(.ListCount - 1, 1) = Vt(1)
                        .List(.ListCount - 1, 2) = Vt(2)
                        .List(.ListCount - 1, 3) = Vt(3)
                        .List(.ListCount - 1, 4) = Vt(4)
                        .List(.ListCount - 1, 5) = Vt(5)
                        .List(.ListCount - 1, 6) = Vt(6)
                        .List(.ListCount - 1, 7) = Vt(7)
                        .List(.ListCount - 1, 8) = Vt(8)
                        .List(.ListCount - 1, 9) = Vt(9)
                        .List(.ListCount - 1, 10) = Vt(10)
                    End With
                    '//
                    Exit For
                End If
            Next

As the user selects another value in ComboBox6 and the value matches the value at column 0 in ListBox5, I would loop through the code again and append the row to ListBox6.

Since .additem does not support columns greater than 10, I was looking for a method to build/rebuild an array that would have all 11 columns and populate ListBox6.

Hope this helps.

Thanks so much.

 

[GTO]

Hi Norie,

Well, I'm not sure I'd trust anything from a guy who can't read help correctly, but I think you must have simply overlooked changing .ColumnCount and the box didn't display all the data.

I mention display, as I notice that aryRes in the below returns all 15 columns regardless of .ColumnCount.

Private Sub UserForm_Initialize()
Dim aryRes
    
    With Me.ListBox1
        .ColumnCount = 2 '15
        .List = Range(Cells(1), Cells(10, 15)).Value
        aryRes = .List
    End With
End Sub

 

[Norie]

Rocky

You want to append data to listbox6 rather than populate it with all the data in listbox8 column 0, listbox5 columns 0-7 etc?

That's kind of different but makes a bit more sense.

Mark

Yeah, I spotted it after a bit of to-and-froing between code/worksheet/userform.

I usually use something like this.

Set rngData = Range("A1:H14")
 
With Listbox1
       .ColumnCount = rngData.Columns.Count
       .List = rngData.Value
End With

Obviously with a hard-coded range you know the column count but handy if you are dealing with dynamic ranges.

 

[Rocky0201]

Hi Norie,

I am not using ListBox8.

I would like to loop thru ListBox5. If I find a match of ComboBox6.value and the value in column 0 row x of ListBox5, I want to use the data in row x of ListBox5 (8 columns of ListBox5), add 3 more items and then append this as a new row in ListBox6.

But since I cannot use .additem, I was thinking I would need to read all rows/columns in ListBox6, append the row, then poplute ListBox6 with the existing rows plus the row I just added.

Thanks Norie.

 

[Norie]

OK, it's a combobox.

Mind you it's not hard to mix up names like this, why not change the names to something representative?

For example, cboLeg for Combobox8.

PS In the last code you posted you refer to Listbox6 which I don't recall being mentioned before.