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,

 

[Rocky0201]

My sincere apology for creating confusion.

On the userform, I have ComboBox's, TextBox's and 2 ListBox's (ListBox5 and ListBox6).

The user will make a couple of selections with the result of ListBox5 populated with a varibale number of rows. ListBox5 will have 8 columns of data. At this point, ListBox6 is empty.

The user then selects a value from the drop down list of ComboBox6. The value selected will be one of the values in column 0 of ListBox5 (one of the rows).

As I loop through the rows in ListBox5 and find a match of the value selected in ComboBox6 to the values in column 0 of ListBox 5, I would like to do the following: (Keep in mind that when I found the match, I have the row in ListBox5)

Since this is the first time around; Build and array with whatever value is in ComboBox8, Null, All 8 columns of ListBox5, whatever value is in TextBox8, whatever value is in TextBox9. - Populate ListBox6.

At this point, ListBox6 would have displayed 1 row with all 11 columns that I just built.

The next selection that the user makes in ComboBox6, Again, I will loop through ListBox5 to find a match in column 0 of ListBox5. Once a match is found;

Since I already have a row of 11 columns in ListBox6, I am guessing I would need to read that row into something, add the next row of 11 columns, then write the, - now - two rows back out to ListBox6.

As the user keeps selecting a value in ComboBox6, I would repeat the process of reading all the rows in ListBox6, adding the new row then writing all of the rows back into ListBox6.

If you look at post #16, The code works great if I were adding 10 columns or less to ListBox6. If the column count that I was adding to ListBox6 were 10 or less, every time the user selects a value in ComboBox6, the code will work fine meaning a new row would be added to ListBox6. Since I have more than 10 columns, the code in post #16 will not execute (Run-time error) therefore, I am looking for a solutions to overcome the 10 column limit.

Thanks for being patient with me, Norie...

 

[Norie]

It really would be good to see some 'real' data.

I've been trying to work this out but with dummy data which is probably inaccurate and made up userform/controls which I've probably got wrong too.

One thing I'm wondering though is why you need so many columns in the listbox?

Couldn't you combine some (or all) of the columns so that you still see the same data just in a sliglhtly different format.

For example the city, state and zip could be concatentated as could the date and time.

 

[Rocky0201]

Norie,

You're absolutely right... I could combine some of the columns.

To answer your first question regarding real data the following would be good.

ListBox5 Columns Multiple Rows (the following is an exanple of one row):

Column 0 = 8004
Column 1 = Walden
Column 2 = NY
Column 3 = 12586
Column 4 = Tuesday
Column 5 = 5/1/2012
Column6 = 8:00 AM

After ListBox5 is populate, The following boxes will be automatically filled in:

ComboBox8 = 1

TextBox17 = Home

The User then selects a value in ComboBox6. For this discussion, they select the value 8004.

The User will have also filled in TextBox8 with a number, say 200.0 and TextBox9 with a number, say 4.5

Once the row in ListBox5 is found, I wish ListBox6 to have the following:

Column 0 = 1 (from ComBoBox8)
Column 1 = Null (Some instances It will be TextBox17)
Column 2 = 8004
Column 3 = Walden
Column 4 = NY
Column 5 = 12586
Column 6 = Tuesday
Column 7 = 5/1/2012
Column 8 = 8:00 AM
Column 9 = 200.0
Column10 = 4.5

The next time the user selects a new value, a new row would be added to ListBox6 with the new data. Note. ComboBox8 would be incremented by 1.

Thanks Norie.

 

[Norie]

Why not upload an example workbook to somewhere like Box.net?

Then you can post a link here an it can be downloaded.

PS What's the Null column for? What value could it hold?

 

[GTO]

If you do post a sample workbook, could you please make it in .xls format?

 

[Rocky0201]

Norie/GTO

In needed to change names and other information for this project is for my customer.

A brief history with the ListBox's on all pages.

I started out using column headers from worksheets when I was creating ListBox's. I had problems clearing any ListBox (.clear) so I decided to insert my own header. Norie, in one of your posts, you asked if my ListBox was attached to a userform or worksheet. Makes sense now. Probably why I could not do a ListBoxX.clear.

The attached workbooks:

When you load the spreadsheet, you should be on the Master Tab. Click on the Main tab to launch the userform.

Select a Date Range (Start & End), a Resource (Resource 8 has the most assignments) and then DBL Click the Each Assignment for however many Assignments you want in a Group. Click Add Group.

Go to Job Route Planner page.

Select the Resource you added to the group then select the Group Number.

Notes: This is under construction so I have yet to activate my Cancel buttons. On the Job Route Planner page, I have not activated the Create Route button nor have I setup my Tallies. I have not yet written code for the Resource Distribution page.

Job Master.xls version Formats changed when I saved it as .xls)
https://www.box.com/s/fe92e609662e10780899

Job Master.xlsm version.
https://www.box.com/s/85ee643e06b21fe4b270

I can't thank you both for the help.

 

[Rocky0201]

I meant to end the previous post with "I can't thank you both ENOUGH for the help".

 

[Norie]

Rocky

I downloaded the file and there seems to be quite a lot going on, and it seems you are at quite an advanced stage.

I keep getting errors though, probably because I'm doing something wrong

Can you give an example(s)?

Anyway, I'll give it another few tries.

PS One thing that makes it hard to work out what's going on is the name of the controls on the forms.

 

[Rocky0201]

Thanks Norie... you're right. I need to get better at self-defining code.

My original problem was trying to determine what an array needs to look like to populate a listbox of 11 columns and how to build the array.

As I was looking at my code, I discovered that my ListBox Sort routine (SortLB) is building an array that once done, repopulates the ListBox. I then copyied this routine, made adjustments and, lo-and-behold, I now have a menthod of building a ListBox with more than 10 columns.

In my main body of code, I build an array with Column heading information. I then set ListBox6 with ColumnCount 11 and the 11 ColumnWidths. Note: LBnum = ListBox #. In this instance, 6

Private Sub BldLBRouteHdr(LBNum)
    
    ReDim V(10)
    
    V(0) = "Leg"
    V(1) = "From"
    V(2) = "To"
    V(3) = "City"
    V(4) = "State"
    V(5) = "Zip"
    V(6) = "Day"
    V(7) = "Date"
    V(8) = "Time"
    V(9) = "Mileage"
    V(10) = "Travel Time"
    With Me.Controls.Item("ListBox" & LBNum)
        .ColumnCount = 11
        .ColumnWidths = "30;40;40;100;30;35;55;60;55;40;40"
    End With
    Call ReBuildLB(6, V, 0)
    
End Sub

I then call: Call ReBuildLB(6, V, 0) where 6 = ListBox #, V is the array, 0 indicates header. If I were adding a row, V would be the array of 11 coulmns of data and I would send a 1 instead of a zero.

Here's the routine.

Public Sub ReBuildLB(LBNum, NewRow, Row)
'// Rebuild ListBox. Use existing Rows. Add new Row
    Dim LbList As Variant
    
    Select Case Row
    
    Case 0 '// Build Header
    
        ReDim LbList(0, 10)
        LbList(0, 0) = NewRow(0)
        LbList(0, 1) = NewRow(1)
        LbList(0, 2) = NewRow(2)
        LbList(0, 3) = NewRow(3)
        LbList(0, 4) = NewRow(4)
        LbList(0, 5) = NewRow(5)
        LbList(0, 6) = NewRow(6)
        LbList(0, 7) = NewRow(7)
        LbList(0, 8) = NewRow(8)
        LbList(0, 9) = NewRow(9)
        LbList(0, 10) = NewRow(10)
 
    Case 1 '// Get existing Rows
        
        ReDim LbList(ResourceMngt.Controls.Item("ListBox" & LBNum).ListCount, 10)
        For i = 0 To ResourceMngt.Controls.Item("ListBox" & LBNum).ListCount - 1
            LbList(i, 0) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 0)
            LbList(i, 1) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 1)
            LbList(i, 2) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 2)
            LbList(i, 3) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 3)
            LbList(i, 4) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 4)
            LbList(i, 5) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 5)
            LbList(i, 6) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 6)
            LbList(i, 7) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 7)
            LbList(i, 8) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 8)
            LbList(i, 9) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 9)
            LbList(i, 10) = ResourceMngt.Controls.Item("ListBox" & LBNum).List(i, 10)
        
        Next
        '// Add new Row
        LbList(i, 0) = NewRow(0)
        LbList(i, 1) = NewRow(1)
        LbList(i, 2) = NewRow(2)
        LbList(i, 3) = NewRow(3)
        LbList(i, 4) = NewRow(4)
        LbList(i, 5) = NewRow(5)
        LbList(i, 6) = NewRow(6)
        LbList(i, 7) = NewRow(7)
        LbList(i, 8) = NewRow(8)
        LbList(i, 9) = NewRow(9)
        LbList(i, 10) = NewRow(10)
        
    End Select
    
    ResourceMngt.Controls.Item("ListBox" & LBNum).Clear
 
    ResourceMngt.Controls.Item("ListBox" & LBNum).List = LbList
    
End Sub

Thus far it seems to work. I need now to work on properly building my arrays before calling this routine.

Please let me know if I am opeing myself up to problems doing this method.

Thanks so much, Norie.

 

[Norie]

Does the code work as it is?

If it does that's the most important thing, you can always go back later to tidy it up.

I actually had something sort of working last night but I couldn't get it to working properly when there were no existing records in the listbox.

Think I just missed something obvious there though and I thought of another approach, not had time to try it though.

By the way, why do you have a row acting as a 'heading' row? Doesn't that makes more complicated? Perhaps you could use labels above the listboxes for headers?