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

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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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
> >
> >
>
>

Hi Bob,

> Norman's formula is missing a closing ).

Thanks for the catch!

---
Regards,
Norman



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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)
>