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

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

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

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)