Filter choice selection based on combobox values

[Prevost]

Hi There! I am trying to create a macro that will allow the user to select choices from combo boxes on a userform. The choice of one combo box determines what will be shown in the next combo box and so on. This will occur a set number of times (depending on what they are looking for), at which point the list of possible choices will be presented. Does anyone have any suggestions as to how I could structure it?

I populated one combo box in UserForm_Initialize() but then I might have to change the others with Combobox1_Change()...any thoughts would be appreciated!

 

[scott_n_phnx]

Yes, you can make cascading Change events, basing them off of the value in the selected ComboBox. Previously I had written a test code that may help. The ListFillRanges that are used are pre-determined by you and you will need to populate as needed, but, in essence, they will fill with whatever named ranges you need. Then, when the first ComboBox is changed, the ListFillRange adjusts. Then, if needed, you can do the same with subsequent ComboBoxes, using the same type of coding.

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Tom Baker" Then
    ComboBox2.ListFillRange = " "
    ComboBox2.ListFillRange = "Range1"
    ComboBox2.Value = "Please select..."
    
End If

If ComboBox1.Value = "Jon Pertwee" Then
    ComboBox2.ListFillRange = " "
    ComboBox2.ListFillRange = "Range2"
    ComboBox2.Value = "Please select..."
End If

If ComboBox1.Value = "William Hartnell" Then
    ComboBox2.ListFillRange = " "
    ComboBox2.ListFillRange = "Range3"
    ComboBox2.Value = "Please select..."
End If

If ComboBox1.Value = "Paul McGann" Then
    ComboBox2.ListFillRange = " "
    ComboBox2.ListFillRange = "Range4"
    ComboBox2.Value = "Please select..."
End If

If ComboBox1.Value = "David Tennant" Then
    ComboBox2.ListFillRange = " "
    ComboBox2.ListFillRange = "Range5"
    ComboBox2.Value = "Please select..."
End If

If ComboBox1.Value = "Matt Smith" Then
    ComboBox2.ListFillRange = " "
    ComboBox2.ListFillRange = "Range6"
    ComboBox2.Value = "Please select..."
End If

If ComboBox1.Value = "" Then
    ComboBox2.Value = ""
End If

End Sub

 

[Prevost]

Thanks for the reply. I would use the Combobox#_Change() within the Userform code? I had another thread about declaring public variables as I had listed all my ranges in a module, which I then called into my Userform. I could theoretically enter all my ranges in the Userform_Initialize(), but would the Private Sub Combobox#_Change() be able to see those Ranges declared in Userform_Initialize(), without having to declare those Ranges as Global?

 

[scott_n_phnx]

I don't see why you cannot use it within a userform, but I have not, so am not certain. You may need to adjust the code to Worksheet("SheetName").ComboBox# to make sure that it picks up correctly. As for the variables, again I don't see why not, so testing will be needed.

 

[Prevost]

I know this is older, but I was trying to create a configurator essentially. Scott, your methodology would work for small lists but for larger lists, using the IF and ELSEIF statements would be cumbersome and time consuming. Assuming you have a list of all your data (which in this case I do because I do not want to type all the data into VBA window), you can create arrays based on the combobox selection. I was trying to create a basic configurator for motor selection and I found that I was typing too much. So I passed arguments into a sub procedure that I then just called and it created my arrays. The arrays contain the row numbers which the values can be found on. So if the user was looking for a 50 HP motor, they would select 50 HP from the drop down menu, the sub procedure would look for all the cells that contained 50 in the HP column and would remember enter the row number into the array. Then, the next selection might be RPM so all the RPM values that correspond to 50 HP would be displayed using the row numbers stored in the array. Eventually, the selection process is reduced to a few selections which the user can see. I also created a variable for the column header to search the sheet for the heading "RPM" or "HP" so that they can be moved around and the code will still reference correctly. This is only part the sub procedure, but I am able to Call this procedure repeatedly and it reduces the number of FOR and IF statements. I am sure there is more that I can do to reduce it but I haven't got there yet!
Thanks,

Private Sub CreateRowArray(x As Integer, ArrayOld(), ArrayNew(), Parameter, SearchParameterColumn)
    Dim i As Integer, d As Integer, n As Integer: n = 0
    Dim Find As Range
    Set Find = Sheets("1 Speed Motor Costs").Cells.Find(what:=SearchParameterColumn, LookIn:=xlValues, lookat:=xlWhole)
    d = Find.Column
    For i = 1 To x
        If Cells(ArrayOld(i), d).Text = Parameter Or Cells(ArrayOld(i), d) = vbNullString Then
            n = n + 1
            ReDim Preserve ArrayNew(n)
            ArrayNew(n) = Cells(ArrayOld(i), d).Row
        End If
    Next i
End Sub