Fill a listbox with data from variable range
 

Hi all,

I'm trying to add items to a listbox, from a source that changes.
The values start from range B4. There can be as little as 5 cells to 36
cells of data that i need to add into the listbox.

I've tried to define the range (i.e. rng =
sheets("Data").range("B4").Selection.End(xlDown)) and get the listbox to pick
the values up from that range;
and have tried a looping statement to pick up cells from B4 until it hits a
blank cell.

The sheet with the data is hidden, and im trying not to use any select or
activate commands to fill the listbox.

Can anyone please help.
Cheers.
Al

Fill a listbox with data from variable range
 

Hi Al

Assuming the listbox is on a UserForm you could try something like the
following when you open the UserForm.....

Private Sub UserForm_Initialize()
Dim r As Range, c As Range
With Sheets("Data")
Set r = .Range(.Range("B4"), .Range("B" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub

--
XL2002
Regards

William



"Al" wrote in message
...
| Hi all,
|
| I'm trying to add items to a listbox, from a source that changes.
| The values start from range B4. There can be as little as 5 cells to 36
| cells of data that i need to add into the listbox.
|
| I've tried to define the range (i.e. rng =
| sheets("Data").range("B4").Selection.End(xlDown)) and get the listbox to
pick
| the values up from that range;
| and have tried a looping statement to pick up cells from B4 until it hits
a
| blank cell.
|
| The sheet with the data is hidden, and im trying not to use any select or
| activate commands to fill the listbox.
|
| Can anyone please help.
| Cheers.
| Al
|

Fill a listbox with data from variable range
 

Thanks William.

Setting up the range line was where i was having trouble. Your's works
fantastically.
Thanks heaps.

Al.


"William" wrote:

Hi Al

Assuming the listbox is on a UserForm you could try something like the
following when you open the UserForm.....

Private Sub UserForm_Initialize()
Dim r As Range, c As Range
With Sheets("Data")
Set r = .Range(.Range("B4"), .Range("B" & Rows.Count).End(xlUp))
For Each c In r
ListBox1.AddItem c
Next c
End With
End Sub

--
XL2002
Regards

William



"Al" wrote in message
...
| Hi all,
|
| I'm trying to add items to a listbox, from a source that changes.
| The values start from range B4. There can be as little as 5 cells to 36
| cells of data that i need to add into the listbox.
|
| I've tried to define the range (i.e. rng =
| sheets("Data").range("B4").Selection.End(xlDown)) and get the listbox to
pick
| the values up from that range;
| and have tried a looping statement to pick up cells from B4 until it hits
a
| blank cell.
|
| The sheet with the data is hidden, and im trying not to use any select or
| activate commands to fill the listbox.
|
| Can anyone please help.
| Cheers.
| Al
|