Looping to fill ListBox

[davidm]

I have assigned a macro to commandbutton whose job is to populate a
ListBox with data from varying ranges from each sheet in a workbook.
(For this purpose, the ColumnCount is conservatively set to 8). It may
sound a bit weird for anyone to attempt to do this but the object is to
create a palpable visual effect of the process of filling the box. How
do I achieve this looping through the sheets -but without activating
them-? The code below treads water at the activesheet and fails to
loop.

Private Sub CommandButton1_Click()
For Each sh In Worksheets
Set rng = sh.Range("a1:f" & sh.[a65536].End(xlUp).Row)
ListBox1.RowSource = rng.Address
Next sh
End Sub

Thanks

David

 

[Dave Peterson]

I think you have a couple of choices.

You could extract those ranges to a new sheet and then pick it up all at once.

Or you can loop through each worksheet and just keep adding to the listbox.

Option Explicit
Private Sub CommandButton1_Click()
Dim Rng As Range
Dim sh As Worksheet
Dim myCell As Range
Dim iCtr As Long

Me.ListBox1.ColumnCount = 5

For Each sh In Worksheets
With sh
Set Rng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With
With Me.ListBox1
For Each myCell In Rng.Cells
.AddItem myCell.Value
For iCtr = 1 To Me.ListBox1.ColumnCount - 1
.List(.ListCount - 1, iCtr) = myCell.Offset(0, iCtr).Value
Next iCtr
Next myCell
End With
Next sh
End Sub

You used A:F, but said you had 8 columns. I was confused, so I used 5.

I have assigned a macro to commandbutton whose job is to populate a
ListBox with data from varying ranges from each sheet in a workbook.
(For this purpose, the ColumnCount is conservatively set to 8). It may
sound a bit weird for anyone to attempt to do this but the object is to
create a palpable visual effect of the process of filling the box. How
do I achieve this looping through the sheets -but without activating
them-? The code below treads water at the activesheet and fails to
loop.

Private Sub CommandButton1_Click()
For Each sh In Worksheets
Set rng = sh.Range("a1:f" & sh.[a65536].End(xlUp).Row)
ListBox1.RowSource = rng.Address
Next sh
End Sub

Thanks

David

 

[davidm]

Thank you Dave. Your code works like charm! The other option of
agglomerating the data in one piece on one sheet worked as well. I
wonder why I never thought about that. You are resourceful.

Sorry for confusing you with the columncount. I used a count of 5 in my
coding while at the same time referring to a setting of 8 but this is
only a conservative provision to allow for a possible increase in size
at some future time.

Once again, thank you for the assistance.

David.