Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling combobox with variable length list
I have a combobox on a userform that shows a list from Sheet1. This list
varies in length week to week, so it will not always be the same number of rows. I know how to assign a RowSource in the Properties window. I need to know how to use VBA to accommodate the different length lists, maybe when the form loads. I do not want any extra rows listed that are not used. Any help is much appreciated! Thanks, Denny |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling combobox with variable length list
Hi Denny,
Try using a dynamic range. For example, suppose your variable length list starts in A1, set the RowSource to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A,1) --- Regards, Norman "Denny Behnfeldt" wrote in message ... I have a combobox on a userform that shows a list from Sheet1. This list varies in length week to week, so it will not always be the same number of rows. I know how to assign a RowSource in the Properties window. I need to know how to use VBA to accommodate the different length lists, maybe when the form loads. I do not want any extra rows listed that are not used. Any help is much appreciated! Thanks, Denny |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling combobox with variable length list
Denny,
Norman's formula is missing a closing ). Also, you might find it easier to define a workbook name for this dynamic range and use that in the properties (I find this more manageable myself). And if you wanted to assign it at run time, when the form loads say, assuming the range is named myRange, you can do that with ListBox1.RowSource = Range("myRange").Address A few options for you. -- HTH RP (remove nothere from the email address if mailing direct) "Norman Jones" wrote in message ... Hi Denny, Try using a dynamic range. For example, suppose your variable length list starts in A1, set the RowSource to: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A,1) --- Regards, Norman "Denny Behnfeldt" wrote in message ... I have a combobox on a userform that shows a list from Sheet1. This list varies in length week to week, so it will not always be the same number of rows. I know how to assign a RowSource in the Properties window. I need to know how to use VBA to accommodate the different length lists, maybe when the form loads. I do not want any extra rows listed that are not used. Any help is much appreciated! Thanks, Denny |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filling combobox with variable length list
Hi Bob,
Norman's formula is missing a closing ). Thanks for the catch! --- Regards, Norman "Bob Phillips" wrote in message ... Denny, Norman's formula is missing a closing ). Also, you might find it easier to define a workbook name for this dynamic range and use that in the properties (I find this more manageable myself). And if you wanted to assign it at run time, when the form loads say, assuming the range is named myRange, you can do that with ListBox1.RowSource = Range("myRange").Address A few options for you. -- HTH RP (remove nothere from the email address if mailing direct) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable length of input area in a combobox | Excel Discussion (Misc queries) | |||
Transpose a variable length list into Excel / Access Table | New Users to Excel | |||
searching for a combobox.value and filling in textboxes from results | Excel Programming | |||
Filling a ComboBox? | Excel Programming | |||
how to? running average from a variable length list | Excel Programming |