Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox "expand range as list grows"
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox "expand range as list grows"
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 "TK" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox "expand range as list grows"
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 TK wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Listbox "expand range as list grows"
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 "Dave Peterson" wrote: 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 TK wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
advanced filter dialog box, "List Range" list two ranges? | Excel Worksheet Functions | |||
"Create a drop-down list from a range of cells" | Excel Worksheet Functions | |||
Using "Cells" to write "Range("A:A,H:H").Select" | Excel Programming | |||
copy a range of cells (a row) to the bottom of a "list" | Excel Programming |