Populating Multi-Column Listbox with Conditions from Another Listbox

Hello Excel gurus,

I am here yet again to seek for your help in dealing with multi-column listbox. I previously posted a similar topic and was very pleased with the solution @LJMetzger (Lewis) provided. However this time, another requirement just surfaced and it turns out yet another showstopper for my level.

As you can see in the attached file, Sheet1 houses the main database and a form button to the right. A userform will launch and will serve as a query window. Everything works great at this point (i.e. the combo boxes, calculation of range, the command button that will display the resut in ListBox3). But the question is in the ListBox4 and ListBox3 relationship.

ExcelForumPopulateSimpleUserFormMultiColumnListBox-2015-09-29-10-59.xlsm

Here is what I want to achieve with this system:

1. Currently, CommandButton45 will reveal all columns and rows that match the selections from the combo boxes (and those 2 text boxes)
2. The user can select maximum of 10 items from ListBox4
3. Notice the list in ListBox4, they are the same as the column titles in Sheet1 database
4. What I want to show in ListBox3 are the items that match the selection from ListBox4
5. For instance, the user selects "Product Unit", "Brand", and "Feature 1" from ListBox4, the result in ListBox3 should only those 3 features and their data based on the selection from the combo boxes.
6. In addition, I also need to show the column title for apparent reasons

I tried one approach by passing all the filtered information in a separate (and hidden) sheet, and from there performs an array of Index-Small lookup. However, this turns out unreliable as it takes so much amount of time to load and it was unappealing.

I will appreciate all inputs you can provide.

Thanks in advance.

In addition, if the ColumnCount is set to greater than 10, both in Properties Window and in code, a run time error persists (i.e. Run-time error '380': Could not set the List property. Invalid property value.). No problem if the column count is set to 0 until 9, but the aforementioned error triggers when the count hits 10. I doubt there is a limit in setting the Column Count of a list box.

You inputs will be appreciated.

Thanks in advance.

Hi ykobure,

I ran into the following issues (and/or noticed the following) while attempting to solve your problem:
a. Column Count is limited to columns 0 to 9 in a ListBox if the ListBox is UNBOUND. See http://www.ozgrid.com/Excel/free-tra...a1lesson24.htm When a ListBox is Bound, the Bound Column contains the value returned as .Value for that row.
b. I had to change the data comparison logic in the following construction:

Please Login or Register  to view this content.

c. For ease of debugging I added CONDITIONAL COMPILATION that seeded values in the UserForm in DisplayUserForm(). Follow instructions in the code to remove the seed values.
d. For data consistency, I replaced the RANDBETWEEN() with constant values that were obtained from RANDBETWEEN().
e. For some unknown reason, I had a problem (which acted like clicking the same item twice) when I selected an item from ListBox4 for the first time. I put in a workaround in ListBox4_Change() to ignore the 2nd Selection if it occurred within one second of the first Selection. The workaround improved the situation, but did not fix it completely. If a click in ListBox4 does not activate a selection, just repeat, and it should work.
f. I had to change the 'ColumnHeads' attribute in ListBox3 to 'False', because 'Column Headers' only work when the 'RowSource' attribute is used to populate the ListBox. We are NOT using the 'RowSource' attribute, because we are filtering the data that appears in the ListBox.

See the attached file which includes the following code:

UserForm module:

Please Login or Register  to view this content.

Ordinary Code Module Code will be included in the next post due to space limitations.

Lewis

Ordinary Code Module Code (continued from previous post):

Please Login or Register  to view this content.