Combo Box ListRows property; changing programmatically

Does anyone know how to programmatically change the ListRows property of a
Combo Box?

Using VBA I would like to run a Distinct query against a DB (which I can
do). That query will return values to a Dynamic Named Range, whose Row Count
provides the value for the ListRows property of a Combo Box (with a preset
maximum of 20).

Thanks for your assistance in this regard.

Michael

I am not 100% sure exactly what you are trying to do but here is ssome code
to change the ListRows property...

ComboBox1.ListRows = 8
or
ComboBox1.ListRows = range("MyRange").rows.count
--
HTH...

Jim Thomlinson


"GPDynamics" wrote:

> Does anyone know how to programmatically change the ListRows property of a
> Combo Box?
>
> Using VBA I would like to run a Distinct query against a DB (which I can
> do). That query will return values to a Dynamic Named Range, whose Row Count
> provides the value for the ListRows property of a Combo Box (with a preset
> maximum of 20).
>
> Thanks for your assistance in this regard.
>
> Michael

I am not 100% sure exactly what you are trying to do but here is ssome code
to change the ListRows property...

ComboBox1.ListRows = 8
or
ComboBox1.ListRows = range("MyRange").rows.count
--
HTH...

Jim Thomlinson


"GPDynamics" wrote:

> Does anyone know how to programmatically change the ListRows property of a
> Combo Box?
>
> Using VBA I would like to run a Distinct query against a DB (which I can
> do). That query will return values to a Dynamic Named Range, whose Row Count
> provides the value for the ListRows property of a Combo Box (with a preset
> maximum of 20).
>
> Thanks for your assistance in this regard.
>
> Michael

I have an issue similar to the original question. I am trying to dynamically
change th size of a combo box (from the Control Toolbox toolbar) using a
variable representing the size of a range on a worksheet. I tried the
suggestion to the original question below in the following code (code is in a
module; the combobox is embedded on a worksheet, not on a form):

Attempt 1:
ActiveSheet.Shapes("cmbCurrentbirthdays").Select
With Selection
.ListFillRange = "'Birthdays'!Fillrange"
.ListRows = 8

End With

Attempt 2:
ActiveSheet.Shapes("cmbCurrentbirthdays").ListRows =
Range("fillrange").Rows.Count

Both attempts usng the .ListRows statement give me the same error "Run-time
error '438': Object doesn't support this property or method"

Any ideas what I'm doing wrong? Appreicate any help!
WB


"Jim Thomlinson" wrote:

> I am not 100% sure exactly what you are trying to do but here is ssome code
> to change the ListRows property...
>
> ComboBox1.ListRows = 8
> or
> ComboBox1.ListRows = range("MyRange").rows.count
> --
> HTH...
>
> Jim Thomlinson
>
>
> "GPDynamics" wrote:
>
> > Does anyone know how to programmatically change the ListRows property of a
> > Combo Box?
> >
> > Using VBA I would like to run a Distinct query against a DB (which I can
> > do). That query will return values to a Dynamic Named Range, whose Row Count
> > provides the value for the ListRows property of a Combo Box (with a preset
> > maximum of 20).
> >
> > Thanks for your assistance in this regard.
> >
> > Michael