ComboBox Data Based on Previous ComboBox Selection

  • I have a UserForm with 2 ComboBoxs.


    I would like ComboBox B's results to be filtered based off the selection in ComboBox A.


    Attached is my Workbook. The UserForm in question is "AddQtyFrm"


    After the ComboBox "POlst" has its selection made the ComboBox "PartLst" will be filtered to show results related to "POlst" value.


    Current Code for "AddQtyFrm"


  • this works if the data was separated in separate columns like your example. but in my sheet I have all the data in one column. i need it to sort out only the values that match the value in the other comboBox.


    if you look at my workbook you will see I am trying to sort through the Part Numbers in column G on sheet "Parts List" that correspond to the PO number in column M of the same sheet.


    your example works but i don't think i can make it work with the structure of my sheet

  • the "Spare Parts List" Sheet is still under construction as the cells and data referenced has changed considerably since first created. The data and sheet in question however is the "Parts List" Sheet.


    However I still attached the most recent version.

  • I can't see what you want to do from the limited data in the example.


    What is "UniquePO"? It appears to be a Named Range but that's not how you refer to Named Ranges in Excel VBA.


    Rowsource for loading comboboxes is not a good way to load a combobox in my opinion.

  • "UniquePO" is how i am sorting through all the duplicate PO numbers so my combobox doesn't have multiples. if you look on the "Formulas" sheet you will see the Formula:

    Code
    1. =IF(SORT(UNIQUE(PartsTbl[PO NUMBER]))=0,"",SORT(UNIQUE(PartsTbl[PO NUMBER])))


    if there is a better way of loading a combobox I am open to suggestions. I am very new to this and I have been teaching myself with the help of youtube and the helpful people from here. i think i got the loading of the combobox that way from a youtuber so it very well may not be the best way.


    what I am trying to accomplish is:


    on my AddQtyFrm I want to select a PO Number from the sorted list. after the selection of the PO Number in the first combobox I want the second combobox to sort through all the part numbers and only show the ones related to the PO number selected in the first combobox. this way I can add the Qty received for that part and the date received in the table on the Parts List sheet.


    attached is my workbook again with loads more data in it.

  • The bad news is that there is no easy way to do a conditional lookup in VBA. You need to program out all the gory detail, detecting when POlst has changed and updating the Partlst accordingly. Then you need to re-verify that the combination exists. Looking at your code it seems that you have some way to go in terms of validating input.

    The good news is that I have written a system which will do all of that for you. It is probably a pretty good fit for your requirements although I would suggest some normalisation of your data structures e.g. that you create a Reqs table containing all the header info and that you also have a separate Receipts table. I attach a sample file. New Budget Tracking Sheet - Dexel Form.xlsm

    The conditional lookup you were looking for is to be found in the Receipts table.


    You can download the free addin from dexelform.com

  • How about

    Remove this line from the initialize event

    Code
    1. PartLst.RowSource = "UniquePart"

    And then use

    Code
    1. Private Sub POLst_Click()
    2. Me.PartLst.List = Evaluate("FILTER(PartsTbl[PART NUMBER],PartsTbl[PO NUMBER]=" & Me.POLst.Value & ")")
    3. End Sub
  • How about

    Remove this line from the initialize event

    Code
    1. PartLst.RowSource = "UniquePart"

    And then use

    Code
    1. Private Sub POLst_Click()
    2. Me.PartLst.List = Evaluate("FILTER(PartsTbl[PART NUMBER],PartsTbl[PO NUMBER]=" & Me.POLst.Value & ")")
    3. End Sub

    I like where you are going with this...where exactly do I enter the New code?

  • Are you sure that everyone using the workbook will have access to the new Array Formulas like sort?


    . Add A List of Unique Values to a Combo Box


    You can load the unique entries like this.


    Where's the code that I wrote for this workbook recently?