Excel VBA UserForm Dependant ComboBox for Multiple Options

Question

I'm trying to setup 3 ComboBoxes in an userform.

1. ComboBox1 = Yes/No/Blank

2. ComboBox2 = A List of Options

   • Option1
   • Option2
   • Option3
   • Option4
   • Option5
   • Option6
   • Option7
   • Option8

3. ComboBox3 = Is where I'm having the issue and I need help with.

ComboBox3 is dependant of a combination of ComboBox1 and ComboBox2

I hope I can explain this well.

For instance.

If ComboBox1 = "" Then do not display any options in ComboBox2 and Combobox3

If ComboBox1 = Yes and ComboBox2 = Option1 Then Give me this list of options

If ComboBox1 = No and ComboBox2 = Option1 Then Give me this other list of options.

If ComboBox1 = Yes and ComboBox2 = Option2 Then Give me this list of options

If ComboBox1 = No and ComboBox2 = Option2 Then Give me this other list of options.

And so on for every option.

Hope that makes sense and someone can shed some light on this.

Let me know if more details are needed.

Thanks.

UPDATE

After Researching I came across a script that does the job but I'm having issues implementing it to my current code. I tested it on a separate form and it all works as intended. How I can integrate this to my current UserForm_Initialize()?

I placed this part at the beginning of the form before the UserForm_Initialize() Sub. (I also tried adding it after)

Private dic As Object

Private Sub ComboBox1_Change()
    With Me
        .ComboBox2.Clear
        .ComboBox3.Clear
        If .ComboBox1.ListIndex <> -1 Then
            .ComboBox2.List = dic(.ComboBox1.Value).keys
        End If
    End With
End Sub

Private Sub ComboBox2_Change()
    With Me
        .ComboBox3.Clear
        If .ComboBox2.ListIndex <> -1 Then
            .ComboBox3.List = dic(.ComboBox1.Value)(.ComboBox2.Value).keys
        End If
    End With
End Sub

This is the second part that goes into the UserForm_Initialize() sub and I keep getting an error "Run-Time Error 9. Subscript out of range". The worksheet "Lists" exists and it has all of the necessary information. If I try this separately on another form, it works.

Private Sub UserForm_Initialize()
    Dim a, i As Long
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("Lists").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 2)) Then
            Set dic(a(i, 2)) = CreateObject("Scripting.Dictionary")
        End If
        If Not dic(a(i, 2)).exists(a(i, 1)) Then
            Set dic(a(i, 2))(a(i, 1)) = CreateObject("Scripting.Dictionary")
        End If
        dic(a(i, 2))(a(i, 1))(a(i, 3)) = i
    Next
    Me.ComboBox1.List = dic.keys
End Sub

This is what my UserForm_Initialize() originally looks like without this code. This is for me to load some other comboboxes.

Private Sub UserForm_Initialize()

    Dim rngResponse As Range
    Dim ws As Worksheet

    Set ws = Worksheets("Lists")

    For Each rngResponse In ws.Range("Response")

    Me.cbRes1.AddItem rngResponse.Value
    Me.cbRes2.AddItem rngResponse.Value
    Me.cbRes3.AddItem rngResponse.Value
    Me.cbRes4.AddItem rngResponse.Value
    Me.cbRes5.AddItem rngResponse.Value
    Me.cbRes1.AddItem rngResponse.Value

    Next rngResponse

    End Sub

Answer 1

What that script is doing is going to worksheet "Lists" and looking at a table of information. Column A would be the values of combobox1, Column B would be the values of combobox2, and Column C would be the resulting values for combobox3, like so:

_______A_______    _______B_______    _______C______
ComboBox1 Value    ComboBox2 Value    ComboBox3 List
Yes                1                  Y1a
Yes                1                  Y1b
Yes                1                  Y1c
Yes                2                  Y2a
Yes                2                  Y2b
Yes                2                  Y2c
Yes                3                  Y3a
Yes                3                  Y3b
Yes                3                  Y3c
No                 1                  N1a
No                 1                  N1b
No                 1                  N1c
No                 2                  N2a
No                 2                  N2b
No                 2                  N2c
No                 3                  N3a
No                 3                  N3b
No                 3                  N3c

It then compiles a dictionary object using unique combinations from columns A an B (so things like Dictionary("Yes")(1) and Dictionary("No")(3) and stores the values for the ComboBox3 list as keys to the corresponding unique set of columns A and B.

Personally I think that is a very clever solution and will fit your needs, though it is rather advanced. To make the code work, you'll need to set a Reference in your Visual Basic Editor to Microsoft Scripting Runtime: Tools -> References -> Scroll down until you see "Microsoft Scripting Runtime" and check its box -> Click OK

You'll also need to make sure the data in sheet "Lists" is formatted as shown, with row 1 being a header row, and the values and corresponding ComboBox3 list values setup as shown in my example (of course, replace those with your actual list values).