Looping thru Listbox

[Rocky0201]

Hi,

Throughout my process, I populate ListBox4 with a variable number of rows (8 columns). When the user clicks the commandbutton, I wish to loop through ListBox4 and write each row (with the 8 columns) to sheet1.

Example (ListBox4):

9999| Anytown| CA| 12345| Monday| 4/30/2012| 8:00AM| John Doe|
8888| Anytown| CA| 12345|Tuesday| 5/1/2012 | 8:00AM| John Doe|

I believe i can loop using the Listcount however, I do not know how to index each row in the listbox.

for i = 0 to ListBox4.ListCount -1

Sheets("Sheet1").cells(RowNum,Coulmn) = ????

Next

Any help would be great.

Thanks,

 

[GTO]

If I am understanding correctly, you do not care which items in the listbox are selected, rather, you want all the items in the listbox to be written to the sheet?

 

[Rocky0201]

That's correct GTO. I need to loop through all of the lines in the listbox. Once I have a line, I will be able to update a worksheet with the 8 columns from the line. I will do this for all lines that I loop through.

Thanks GTO.

 

[GTO]

Hi Rocky,

That's correct GTO. I need to loop through all of the lines in the listbox. Once I have a line, I will be able to update a worksheet with the 8 columns from the line. I will do this for all lines that I loop through.

Thanks GTO.

Based upon your first post, as we are writing all to Sheet1, I am guessing at contiguous. If that is not the case, then we would need to loop. If we are plunking the vals in one place though, we can use .Column or .List.

In a junk wb, create a userform with a default named listbox and commandbutton. Plunk into the userform's module:

Option Explicit
Dim SomeArray(0 To 5, 0 To 7) As String
    
Private Sub CommandButton1_Click()
    Range("A1").Resize(6, 8).Value = Me.ListBox1.List
    Unload Me
End Sub
    
Private Sub UserForm_Initialize()
Dim x As Long
Dim y As Long
    
    For x = 0 To 5
        For y = 0 To 7
            SomeArray(x, y) = Application.Choose(y + 1, "A", "B", "C", "D", "E", "F", "G", "H") & x + 1
        Next
    Next
    Me.ListBox1.List = SomeArray
End Sub

As you see, we built an array, plunked it into the listbox, and upon pressing the commandbutton, used .List as the array.

Does that help?

Mark

 

[Rocky0201]

Mark, Thanks so very much. I just got back to my project. Had to deal with month-end stuff.

I will try your recommendation and let you know how it works. I'm working on it now.

Thanks again.

 

[Rocky0201]

Hi Mark.

I see how extracting the rows in a listbox works. Unfortunetly, I need to skip the first row in the listbox and then seclect each row after. So, row's 2 thru x is what I will be updating my sheet with the 8 columns.

If I should need to post code, please let me know.

Thanks so much for the help, Mark.

 

[GTO]

Hi Mark.

I see how extracting the rows in a listbox works. Unfortunetly, I need to skip the first row in the listbox and then seclect each row after. So, row's 2 thru x is what I will be updating my sheet with the 8 columns.

If I should need to post code, please let me know.

Thanks so much for the help, Mark.

Okay, I wasn't catching that part. To skip the listbox's first row, you could fill an array (faster than writing cell-by-cell) and plunk the array to the sheet. Try:

Option Explicit
    
Private Sub CommandButton1_Click()
Dim x As Long
Dim y As Long
Dim OutPut As Variant
    
    '// Resize output array to listbox contents.  Note I used a 1-based array, while    //
    '// .List and .Column are 0-based.                                                  //
    ReDim OutPut(1 To Me.ListBox1.ListCount, 1 To 8)
    
    '// Since .List is 0-based, if we start at 1, we skip the first row in the listbox  //
    For x = 1 To Me.ListBox1.ListCount - 1
        For y = 1 To 8
            OutPut(x, y) = Me.ListBox1.List(x, y - 1)
        Next
    Next
    
    '// plunk results wherever.                                                         //
    Cells(Rows.Count, 1).End(xlUp).Offset(1) _
        .Resize(Me.ListBox1.ListCount - 1, Me.ListBox1.ColumnCount).Value = OutPut
    Unload Me
End Sub
    
Private Sub UserForm_Initialize()
Dim SomeArray(0 To 5, 0 To 7) As String
Dim x As Long
Dim y As Long
    
    For x = 0 To 5
        For y = 0 To 7
            SomeArray(x, y) = Application.Choose(y + 1, "A", "B", "C", "D", "E", "F", "G", "H") & x + 1
        Next
    Next
    Me.ListBox1.List = SomeArray
End Sub

Hope that helps,

Mark

 

[Rocky0201]

Thanks Mark... I'll give this a go and let you know...

 

[Rocky0201]

Mark,

Both of your routines work great. I am using the second routine as it best fits my application.

I can't thank you enough for the help!!

 

[GTO]

Thank you for the feedback Rocky, and of course you are most welcome