Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping to fill ListBox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping to fill ListBox
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping to fill ListBox
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. -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=498026 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Unable to fill Listbox thru VBA | Excel Programming | |||
Looping through listbox controls | Excel Programming | |||
Is there an easier way to fill this listbox | Excel Programming | |||
For..next.. help to fill listbox | Excel Programming | |||
Excel VBA-Looping though MultiSelect on ListBox | Excel Programming |