Filling combobox with variable length list

[Denny Behnfeldt]

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

 

[Norman Jones]

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)

 

[Bob Phillips]

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]

Hi Bob,

Norman's formula is missing a closing ).

Thanks for the catch!