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