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.

davidm wrote:
>
> 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
> ------------------------------------------------------------------------
> davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
> View this thread: http://www.excelforum.com/showthread...hreadid=498026

--

Dave Peterson

[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.