Listbox adjusting amount of elements and removing empty choices

[Jdevil]

Hi,

I have a little issue with Listboxes. Here is my situation.

I have a listbox with choices that are not always the same. What I mean by that is that if you, for example, change your choice in another listbox, the choices will change.

(the excel file I added has an example)

Depending on what you choose in the first listbox, the second one will have a different amount of choices aswell. I want the second listbox to show the correct choices, without empty spaces, and with the correct size.

I'm using regular listboxes, so I'm not in a userform.

What macro (because I guess I'll have to use a macro) should I put on the first listbox that will automatically adapt my second listbox accordingly?

Refering to this file:
listbox issue.xlsb

You can try changing the first listbox and see what happens.
I created 3 different listboxes that each show what the unique listbox should be showing.

Thank you!

[LJMetzger]

Hi Jdevil,

Each time 'Drop Down 2' changes value the value in cell 'L132' changes. We can take advantage of that in the Worksheet_Calculate() routine. The Worksheet_Change() routine does not get triggered because the value in 'L132' is changed by the ListBox and not changed manually.

Try the following code in the Sheet Module:

Please Login or Register  to view this content.

This is a simple solution using the data you provided in your sample workbook. If your needs are more complicated, upload another sample workbook, and we can show you what to do if you need additional help.

Lewis

[newdoverman]

I'm not entirely sure what you want but took a guess.

I used the ranges where you have labelled it should look like this and produced a single range that changes with the choices made in the boxes that I have surrounded with a heavy border. The Yellow area is the result. This list can be assigned to a listbox.

This is the formula that I used to create the variable display in the yellow area. It is dependant upon the choices that you make.

Please Login or Register  to view this content.

[newdoverman]

Here is another version of your file that has the yellow list attached to the listbox and also with a Data Validation drop down listing that dynamically changes with the number of values in the yellow area.

I hope that some of this helps.

[Jdevil]

Hi Jdevil,

Each time 'Drop Down 2' changes value the value in cell 'L132' changes. We can take advantage of that in the Worksheet_Calculate() routine. The Worksheet_Change() routine does not get triggered because the value in 'L132' is changed by the ListBox and not changed manually.

Try the following code in the Sheet Module:

Please Login or Register  to view this content.

This is a simple solution using the data you provided in your sample workbook. If your needs are more complicated, upload another sample workbook, and we can show you what to do if you need additional help.

Lewis

I was going for something similar, but using worksheet calculate on that worksheet makes it trigger too often and it made everything slow or sometime my excel crashed aswell

I don't have much time right now to analyze it so I'll work on it later or tomorrow and see if I could do something with that or newdoverman's options

I'll come back whenever I worked on it, thanks!