SOLVED

Excel VBA Userform filter listbox content by dependent comboboxes (x3)

Copper Contributor

Dear Forum - I am trying to use column data from the worksheet as lists for 3x comboboxes, (2 of which are dependent upon the previous), to filter the worksheet - then display the results in a listbox all on a userform. (Eventually I wish to pull the entire row into a userform / the same userform for editing, upon double-clicking a row in the listbox.)  

 

I can get the 3x comboboxes working, but not the listbox - my preference is for each combobox selection to update the listbox, so by the time all 3 are populated, the listbox displays just those rows that match all 3 combobox inputs.  I am trying initially just to get the listbox to show data upon combobox 1 being selected, then I will expand this once it works to all 3 comboboxes.  (The worksheet I have is 66 columns by approx. 2500 rows - but I have a reduced scale version that I am using to test the code - as attached.)  

 

The final piece to the puzzle will be adding further code to filter by the last column (OPEN/CLOSED/NEW) upon initialising the userform - some direction for this would be appreciated too, please.) 

 

I am running into a series of errors, some of which I believe I have solved.  Unfortunately, the code is incredibly inelegant as I am taking bits and pieces from different online sources, so it is a bit of plagiarism together with some hope and a little luck... most issues appear to focus on Objects (not sure I've defined or called them properly).

 

Any help will be most appreciated - thanks in advance.

7 Replies
best response confirmed by David-W196 (Copper Contributor)
Solution

@David-W196 

See the attached version.

Remark: saving the workbook discarded the digital signature.

Many thanks Hans - it will take me a while to fully understand this, but it definitely works exactly as needed - even so far as taking all 3 comboboxes into account. Bravo and most appreciated.
Hans - thanks again for your prompt efforts. One question - you may have noted above that I am to apply the code to a 66-column worksheet; is there anyway to be selective about which columns appear in the listbox? I.e., I might choose columns 1,3,6 and 8 to show?

@David-W196 

The loop

            For c = 1 To 9
                arr(c, n) = sh.Cells(r, c).Value
            Next c

fills the array with the first 9 columns (A to I). To use specific columns, change this to

            arr(1, n) = sh.Cells(r, 1).Value
            arr(2, n) = sh.Cells(r, 3).Value
            arr(3, n) = sh.Cells(r, 6).Value
            arr(4, n) = sh.Cells(r, 8).Value
You are an absolute star Hans - thank you so much.

If you are still prepared to assist [you know, write my entire code for me... :) ] a little further, my next task is to double-click the list entry to populate either this form or a separate one with all 66 columns from the row selected. Your advice here would be valued highly: "Q: should this be to a separate form or the same form - on the basis of 'neatness', 'operability' and 'good practice?"

Whether you are happy to continue to support or not, I have noted that it is you who provides me support most frequently on this forum... it is noted and it is appreciated. Thanks again. Best regards, D.

@David-W196 

I'd use a separate userform. I cannot create the entire application for you, but I have set up an example - see the attached version. It only handles 9 columns, you'll have to expand that yourself.

@Hans Vogelaar 

 

Well!  That is incredibly generous of you - thank you for all your help, support and coding.

 

My goal is to learn - I am therefore trying to decipher everything you offer, to get a better understanding. 

 

Thanks again, best regards, D.

1 best response

Accepted Solutions
best response confirmed by David-W196 (Copper Contributor)
Solution

@David-W196 

See the attached version.

Remark: saving the workbook discarded the digital signature.

View solution in original post