Forum Discussion
Excel VBA Userform filter listbox content by dependent comboboxes (x3)
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
- 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?
The loop
For c = 1 To 9 arr(c, n) = sh.Cells(r, c).Value Next cfills 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
- 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.
