D
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
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