SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-3250102%22%20slang%3D%22en-US%22%3EExcel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250102%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20Forum%20-%20I%20am%20trying%20to%20use%20column%20data%20from%20the%20worksheet%20as%20lists%20for%203x%20comboboxes%2C%20(2%20of%20which%20are%20dependent%20upon%20the%20previous)%2C%20to%20filter%20the%20worksheet%20-%20then%20display%20the%20results%20in%20a%20listbox%20all%20on%20a%20userform.%20(Eventually%20I%20wish%20to%20pull%20the%20entire%20row%20into%20a%20userform%20%2F%20the%20same%20userform%20for%20editing%2C%20upon%20double-clicking%20a%20row%20in%20the%20listbox.)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20get%20the%203x%20comboboxes%20working%2C%20but%20not%20the%20listbox%20-%20my%20preference%20is%20for%20each%20combobox%20selection%20to%20update%20the%20listbox%2C%20so%20by%20the%20time%20all%203%20are%20populated%2C%20the%20listbox%20displays%20just%20those%20rows%20that%20match%20all%203%20combobox%20inputs.%26nbsp%3B%20I%20am%20trying%20initially%20just%20to%20get%20the%20listbox%20to%20show%20data%20upon%20combobox%201%20being%20selected%2C%20then%20I%20will%20expand%20this%20once%20it%20works%20to%20all%203%20comboboxes.%26nbsp%3B%20(The%20worksheet%20I%20have%20is%2066%20columns%20by%20approx.%202500%20rows%20-%20but%20I%20have%20a%20reduced%20scale%20version%20that%20I%20am%20using%20to%20test%20the%20code%20-%20as%20attached.)%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20final%20piece%20to%20the%20puzzle%20will%20be%20adding%20further%20code%20to%20filter%20by%20the%20last%20column%20(OPEN%2FCLOSED%2FNEW)%20upon%20initialising%20the%20userform%20-%20some%20direction%20for%20this%20would%20be%20appreciated%20too%2C%20please.)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20running%20into%20a%20series%20of%20errors%2C%20some%20of%20which%20I%20believe%20I%20have%20solved.%26nbsp%3B%20Unfortunately%2C%20the%20code%20is%20incredibly%20inelegant%20as%20I%20am%20taking%20bits%20and%20pieces%20from%20different%20online%20sources%2C%20so%20it%20is%20a%20bit%20of%20plagiarism%20together%20with%20some%20hope%20and%20a%20little%20luck...%20most%20issues%20appear%20to%20focus%20on%20Objects%20(not%20sure%20I've%20defined%20or%20called%20them%20properly).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20will%20be%20most%20appreciated%20-%20thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3250102%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3251020%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3251020%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell!%26nbsp%3B%20That%20is%20incredibly%20generous%20of%20you%20-%20thank%20you%20for%20all%20your%20help%2C%20support%20and%20coding.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20goal%20is%20to%20learn%20-%20I%20am%20therefore%20trying%20to%20decipher%20everything%20you%20offer%2C%20to%20get%20a%20better%20understanding.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%2C%20best%20regards%2C%20D.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250950%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1225902%22%20target%3D%22_blank%22%3E%40David-W196%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20use%20a%20separate%20userform.%20I%20cannot%20create%20the%20entire%20application%20for%20you%2C%20but%20I%20have%20set%20up%20an%20example%20-%20see%20the%20attached%20version.%20It%20only%20handles%209%20columns%2C%20you'll%20have%20to%20expand%20that%20yourself.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250869%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250869%22%20slang%3D%22en-US%22%3EYou%20are%20an%20absolute%20star%20Hans%20-%20thank%20you%20so%20much.%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20are%20still%20prepared%20to%20assist%20%5Byou%20know%2C%20write%20my%20entire%20code%20for%20me...%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%5D%20a%20little%20further%2C%20my%20next%20task%20is%20to%20double-click%20the%20list%20entry%20to%20populate%20either%20this%20form%20or%20a%20separate%20one%20with%20all%2066%20columns%20from%20the%20row%20selected.%20Your%20advice%20here%20would%20be%20valued%20highly%3A%20%22Q%3A%20should%20this%20be%20to%20a%20separate%20form%20or%20the%20same%20form%20-%20on%20the%20basis%20of%20'neatness'%2C%20'operability'%20and%20'good%20practice%3F%22%3CBR%20%2F%3E%3CBR%20%2F%3EWhether%20you%20are%20happy%20to%20continue%20to%20support%20or%20not%2C%20I%20have%20noted%20that%20it%20is%20you%20who%20provides%20me%20support%20most%20frequently%20on%20this%20forum...%20it%20is%20noted%20and%20it%20is%20appreciated.%20Thanks%20again.%20Best%20regards%2C%20D.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250829%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250829%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1225902%22%20target%3D%22_blank%22%3E%40David-W196%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20loop%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20%20%20%20%20%20%20%20%20For%20c%20%3D%201%20To%209%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20arr(c%2C%20n)%20%3D%20sh.Cells(r%2C%20c).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20Next%20c%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efills%20the%20array%20with%20the%20first%209%20columns%20(A%20to%20I).%20To%20use%20specific%20columns%2C%20change%20this%20to%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3E%20%20%20%20%20%20%20%20%20%20%20%20arr(1%2C%20n)%20%3D%20sh.Cells(r%2C%201).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20arr(2%2C%20n)%20%3D%20sh.Cells(r%2C%203).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20arr(3%2C%20n)%20%3D%20sh.Cells(r%2C%206).Value%0A%20%20%20%20%20%20%20%20%20%20%20%20arr(4%2C%20n)%20%3D%20sh.Cells(r%2C%208).Value%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250450%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250450%22%20slang%3D%22en-US%22%3EHans%20-%20thanks%20again%20for%20your%20prompt%20efforts.%20One%20question%20-%20you%20may%20have%20noted%20above%20that%20I%20am%20to%20apply%20the%20code%20to%20a%2066-column%20worksheet%3B%20is%20there%20anyway%20to%20be%20selective%20about%20which%20columns%20appear%20in%20the%20listbox%3F%20I.e.%2C%20I%20might%20choose%20columns%201%2C3%2C6%20and%208%20to%20show%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250148%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250148%22%20slang%3D%22en-US%22%3EMany%20thanks%20Hans%20-%20it%20will%20take%20me%20a%20while%20to%20fully%20understand%20this%2C%20but%20it%20definitely%20works%20exactly%20as%20needed%20-%20even%20so%20far%20as%20taking%20all%203%20comboboxes%20into%20account.%20Bravo%20and%20most%20appreciated.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3250137%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20VBA%20Userform%20filter%20listbox%20content%20by%20dependent%20comboboxes%20(x3)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3250137%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1225902%22%20target%3D%22_blank%22%3E%40David-W196%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESee%20the%20attached%20version.%3C%2FP%3E%0A%3CP%3ERemark%3A%20saving%20the%20workbook%20discarded%20the%20digital%20signature.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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.