ComboBox ListRows Property

Hi guys,

Wondering if someone can help me with dynamic ComboBox lists, and automatic resizing using the ListRows property.

If I have a non-dynamic (i.e., fixed) list, I can show all entries in the list by setting ListRows = 0.

For example, if the list is equal to {1,2,3,4,5}, then all 5 entries are shown in the drop-down list.

However, if I'm using a defined name to refer to a dynamic list, this no longer works.

For example, suppose I introduce the defined name "My_Dynamic_Menu" using the formula:

=OFFSET(First_Value,0,0,Dynamic_Menu_Length,1)

where Dynamic_Menu_Length is the variable length of My_Dynamic_Menu.

Then I can set the ListFillRange property in the ComboBox equal to My_Dynamic_Menu, so that the list updates automatically using the above formula.

However, if I set ListRows = 0, the number of entries shown in the drop-down menu does not update automatically to reflect the new list.

Does anyone know how I can make this work?

Any help would be greatly appreciated.

Thanks & regards,

Grant

Dear RoyUK,

Thanks for your reply.

I am using an ActiveX Control, so I don't have a List property, but perhaps the same would work by replacing List with ListFillRange?

What I was really hoping for was a way for updating the number of rows automatically without using code. Is this possible?

Regards,

Grant

Hi Palmetto,

Thanks for the reply.

I understand that ListRows normally controls the number of rows to display. However, this does not work with my dynamic list.

The list updates automatically. However, the number of items shown in the drop-down box does not, and worse there is no scroll bar. Therefore I run into the situation where I change the list from say {1,2,3,4,5} to {1,2,3,4,5,6,7,8,9,10}, but I only have access to elements 1,2,3,4,5 in the drop-down box (without scroll bar).

How can I get access to the complete list?

Thanks & regards,

Grant