Listbox "expand range as list grows"

[Guest]

Hi:

The following populates a listbox with values
from Sheet1!A1:b19.

Private Sub CommandButton3_Click()
UserForm1.ListBox1.RowSource = "Sheet1!A1:b10"
..........
End Sub

How should
UserForm1.ListBox1.RowSource = "Sheet1!A1:b10"
be written to automatically increase as more
items are added to the list ?

Thanks
TK

 

[Guest]

Hi:
The following line should have read.

The following populates a listbox with values
from Sheet1!A1:b10.

Private Sub CommandButton3_Click()
UserForm1.ListBox1.RowSource = "Sheet1!A1:b10"
..........
End Sub

Can the line:
UserForm1.ListBox1.RowSource = "Sheet1!A1:b10"
be written to automatically increase as more
items are added to the list ?

Thanks
TK

 

[Dave Peterson]

Maybe you could use a dynamic range name:

I did Insert|Name|define and called the name: MyList

I used this formula in the refers to box:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),2)

Then in code, I could use:

UserForm1.ListBox1.RowSource _
= Worksheets("sheet1").Range("MyList").Address

This does depend on the fact that column A is always filled.

See Debra Dalgleish's site for more info:
http://www.contextures.com/xlNames01.html#Dynamic

===

Or I could do it in code this way:

Option Explicit
Private Sub CommandButton3_Click()

Dim myRng As Range
With Worksheets("sheet1")
Set myRng = .Range("a1:b" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With
UserForm1.ListBox1.RowSource = myRng.Address(external:=True)
End Sub

 

[Guest]

Thanks Dave

I just retreved your post so I haven't had time to play around with it yet
or visit the contextures site, just wanted to acknowledge your reply.

TK