Mar 08 2022 03:24 AM
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.
Mar 08 2022 04:26 AM
SolutionMar 08 2022 04:36 AM
Mar 08 2022 09:03 AM
Mar 08 2022 11:51 AM
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
Mar 08 2022 12:42 PM
Mar 08 2022 02:05 PM - edited Mar 09 2022 04:29 AM
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.
Mar 08 2022 03:25 PM
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.
Mar 08 2022 04:26 AM
SolutionSee the attached version.
Remark: saving the workbook discarded the digital signature.