ActiveX Listbox update list range

**Fillemang** · 08-01-2018, 09:33 AM

Hi

i have a formula that creates a varible list depending on a selected criteria.
Instead of using the formula in a datavalidation dropdown list i use a Active X listbox.

First i have a Listbox1 that displays all my criterias and when i select my criteria in listbox1 all the unique matches is displayed in ListBox.2.

Now the problem is that sometimes i can have up to 50 unique matches and sometimes just 3, but the scrollbar doesnt change so im not able to scroll throu all the unique items.
How can i do so that the scrollbar and number of shown items updates when i cange the criteria?

i'v never worked with VBA code, so if someone knows how to solve it please don't override my brain

**bakerman2** · 08-01-2018, 09:58 AM

Have a look at the ListRows-property of the Combobox.

You can set this previously to filling the Combobox with items depending on the number of unique matches.

It all depends on how you create your unique list.

**Fillemang** · 08-01-2018, 10:09 AM

This is the formula im using
=OFFSET(Ventiler!$B$1;MATCH(Sheet1!$B$8;Ventiler!$A$2:$A$400;0);0;COUNTIF(Ventiler!$A$2:$A$400;Sheet1!$B$8);1) that i have put in Namemanger under DepDropDownList.

Im trying to avoid Combobox since it crashes Excel when arrowing down, so thats why i like to use the Listboxinstead

**bakerman2** · 08-01-2018, 11:39 AM

Can you post an example file. Remember to desensitize data if needed.

**bongo_bg** · 08-01-2018, 01:31 PM

Hello Fillemang,

I'm not sure I perfectly understand what do you need but check if the attached picture is what you need. You can type the maximum number of possible rows although I doubt it will gather more than ~50 on one screen. If the actual list is less than that it will automatically shrink and enlarge.