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