Fill a listbox with data from variable range

[Guest]

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

 

[William]

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

(e-mail address removed)

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

 

[Guest]

Thanks William.

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

Al.