Userform - how to create dependent/cascading combo boxes

[Yodelayheewho]

Hello all,
I need to create four combo boxes that are dependent. It's basically a linear filter starting from Combobox1 - Combobox4
I found a video that showed three dependent combo boxes and used that code for my project. However, I'm hung up. I can't get ComboBox3 to populate and therefore, I can't get ComboBox4 to populate either.

I hope fresh eyes may be able to see the error.
I've attached the sample file.
Thank you in advance!

 

[Marc L]

Hello, according to your attachment with the 4 'yellow' columns already sorted :​

Dim Rg(2 To 3) As Range

Private Sub ComboBox1_Change()
    Dim V, R&
        ComboBox2.Clear:  ComboBox3.Clear:  ComboBox4.Clear:  If ComboBox1.ListIndex < 0 Then Exit Sub
    With Sheet1.ListObjects(1).Range.Columns(1)
        Set Rg(2) = .Parent.Range(.Find(ComboBox1.Text, , xlValues, 1, , 1)(1, 2), .Find(ComboBox1.Text, , xlValues, 1, , 2)(1, 2))
    End With
        V = Rg(2)
    If IsArray(V) Then
            ComboBox2.AddItem V(1, 1)
        For R = 2 To UBound(V)
            If V(R, 1) <> V(R - 1, 1) Then ComboBox2.AddItem V(R, 1)
        Next
    Else
        ComboBox2.AddItem V
    End If
        ComboBox2.ListIndex = ComboBox2.ListCount > 1
End Sub

Private Sub ComboBox2_Change()
    Dim V, R&
        ComboBox3.Clear:  ComboBox4.Clear:  If ComboBox2.ListIndex < 0 Then Exit Sub
    With Rg(2)
        Set Rg(3) = .Parent.Range(.Find(ComboBox2.Text, .Cells(.Count), xlValues, 1, , 1)(1, 17), _
                                  .Find(ComboBox2.Text, , xlValues, 1, , 2)(1, 17))
    End With
        V = Rg(3)
    If IsArray(V) Then
            ComboBox3.AddItem V(1, 1)
        For R = 2 To UBound(V)
            If V(R, 1) <> V(R - 1, 1) Then ComboBox3.AddItem V(R, 1)
        Next
    Else
        ComboBox3.AddItem V
    End If
        ComboBox3.ListIndex = ComboBox3.ListCount > 1
End Sub

Private Sub ComboBox3_Change()
    Dim V, R&
        ComboBox4.Clear:  If ComboBox3.ListIndex < 0 Then Exit Sub
    With Rg(3)
        V = .Parent.Range(.Find(ComboBox3.Text, .Cells(.Count), xlValues, 1, , 1)(1, 2), _
                          .Find(ComboBox3.Text, , xlValues, 1, , 2)(1, 2))
    End With
    If IsArray(V) Then
            ComboBox4.AddItem V(1, 1)
        For R = 2 To UBound(V)
            If V(R, 1) <> V(R - 1, 1) Then ComboBox4.AddItem V(R, 1)
        Next
    Else
        ComboBox4.AddItem V
    End If
        ComboBox4.ListIndex = ComboBox4.ListCount > 1
End Sub

Private Sub UserForm_Initialize()
    Dim V, R&
        V = Sheet1.ListObjects(1).Range.Columns(1)
    If IsArray(V) Then
        For R = 2 To UBound(V)
            If V(R, 1) <> V(R - 1, 1) Then ComboBox1.AddItem V(R, 1)
        Next
    Else
        ComboBox1.AddItem V
    End If
        ComboBox1.ListIndex = ComboBox1.ListCount > 1
End Sub

Private Sub UserForm_Terminate()
    Erase Rg
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Yodelayheewho]

I am BLOWN AWAY!!! This works perfectly!!! I've been working for weeks, between other projects, to figure out how to do this.
This code will take my userform to a whole new level of automation and functionality.
I've posted the updated sample for others who may need the same.

Thank you so much Mark L!

 

[Yodelayheewho]

Mark L,
In this part of the code, starting as Set Rg(3), what do these lines do and what do the numbers represent? For example, what is "17" referencing? I notice after xlValues there is a "1", then a blank space, blank space,"1".

Private Sub ComboBox2_Change()
    Dim V, R&
        ComboBox3.Clear:  ComboBox4.Clear:  If ComboBox2.ListIndex < 0 Then Exit Sub
    With Rg(2)
        Set Rg(3) = .Parent.Range(.Find(ComboBox2.Text, .Cells(.Count), xlValues, 1, , 1)(1, 17), _
                                  .Find(ComboBox2.Text, , xlValues, 1, , 2)(1, 17))

Thank you!

 

[Marc L]

Marc L,

According to the Combobox2 content the relative STName range is allocated to Rg(3) …​

​

.Find(parameter#1, parameter#2, …, parameter#X)(Row, Column)​

​

According to the Combobox2 content fhe first Find search the first matching cell in CustId column and the second Find search the last …​

Read the Range.Find VBA help to know more about its parameters.​

​

(1, 17) is a cell reference from the found cell like Offset(0, 16) …​

​

 

[Yodelayheewho]

Sorry about the misspelling of your name Marc. Thank you for providing this explanation!

 

[Marc L]

How many rows in your real worksheet ?​

​

 

[Yodelayheewho]

My real worksheet has 8 rows, but a lot of code. I'm trying to upload it here, but the website says the file is too large. It is 1,493 KB.

I want to show it to you because I got your code to work and so thrilled because I'm getting close to launching this upgraded version for actual use, but I'm running into a runtime error 91 that I can't resolve. Since I can't upload the workbook, I've provided screenshots to show the sequence and error.

I can avoid the error, if I open the userform, go to either Cust Name, STName or EUName and make a selection. Once I make a selection and things populate, I can go to the listbox and select a record and everything works fine.

As a note, the EUName and EUID are pulling from the same columns as Cust Name and CustID. Our Customers and End Users are listed together because they can often be a Customer and End User.

Anyways, I'm hoping with your experience, you can help me figure out what's causing the issue.

Thank you in advance for being so willing to help me!

 

[Yodelayheewho]

I searched for what to do about the file being too large and found an old post from you about using dropbox. I should have figured that out myself
TEST4_Systems Order Entry Master

 

[Marc L]

The Dropbox download fails : did you you set it as public ?​

​

Try to save the workbook as binary format .xlsb in case it becomes lighter in order to attach it here but​

with only 8 rows how can your workbook be such heavier ?!​

​

According to your picture with the yellow codeline the error seems to rise 'cause the text is not found …​

​

 

[Yodelayheewho]

Please try this link: TEST4_Systems Order Entry Master

 

[Marc L]

Was a webbrowser issue …​

​

But I was right : saved as binary format .xlsb its size is divided by 2 ‼ As a reminder for next time …​

​

 

[Marc L]

My post #2 demonstration is made upon your initial post attachment for some 'cascading' combo boxes in this mandatory order :​

1. Customer Name
2. CustId
3. STName
4. STID

Opening your post #11 attachment, after clicking on the Data Entry Form button clicking directly on the top left list box​

the error #91 rises then clicking on the debug button I see the Rg(2) variable equals to Nothing​

so that just means the cascading order is not respected …​

​

 

[Marc L]

According to your initial post attachment I can share another way using a Windows Dictionary​

- or a VBA Collection if you use a Mac, whatever -​

in order to 'cascasde' all at once in the UserForm_Initialize event procedure, over complicating it​

but simplifying each combo box Change event procedure …​

​

 

[Yodelayheewho]

My post #2 demonstration is made upon your initial post attachment for some 'cascading' combo boxes in this mandatory order :​

1. Customer Name
2. CustId
3. STName
4. STID

Opening your post #11 attachment, after clicking on the Data Entry Form button clicking directly on the top left list box​

the error #91 rises then clicking on the debug button I see the Rg(2) variable equals to Nothing​

so that just means the cascading order is not respected …​

​

Yes, I noticed the Rg(2) equals nothing as well. I've been struggling to figure out why.

 

[Yodelayheewho]

According to your initial post attachment I can share another way using a Windows Dictionary​

- or a VBA Collection if you use a Mac, whatever -​

in order to 'cascasde' all at once in the UserForm_Initialize event procedure, over complicating it​

but simplifying each combo box Change event procedure …​

​

I am using a PC, not a Mac. I am very new with VBA and I'm sure my code could be improved. I'm open to recommendations.

 

[Marc L]

I noticed the Rg(2) equals nothing as well. I've been struggling to figure out why

You must fill the first combo box before using the next one - cascade rule ! - but maybe you do not ever need to use those combo boxes …​

​

 

[Yodelayheewho]

I figured out the issue! The following code within Private Sub Userform_Initialize() was causing the error.

'***Set dimension to textboxes with CustID combobox value selected. See cboCustID change for remaining code.
Dim i As Long
Set wsh = Sheets("CUSTOMERDATA")
LastRow = wsh.Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    Me.cboCustID.AddItem wsh.Cells(i, "B").Value
    Next i

but maybe you do not ever need to use those combo boxes …

I would like to hear your thoughts on a better way to do this, Marc.

Thank you!

 

[Marc L]

As you understood how a cascade should work so according to my post #14 you can​

compare / study this Windows only alternative not needing the columns to be already sorted :​

Dim oDic As Object

Private Sub ComboBox1_Change()
    ComboBox2.Clear:  ComboBox3.Clear:  ComboBox4.Clear:  If ComboBox1.ListIndex < 0 Then Exit Sub
    ComboBox2.List = oDic(ComboBox1.Text)
    ComboBox2.Enabled = ComboBox2.ListCount > 1
    ComboBox2.ListIndex = 0
End Sub

Private Sub ComboBox2_Change()
    ComboBox3.Clear:  ComboBox4.Clear:  If ComboBox2.ListIndex < 0 Then Exit Sub
    ComboBox3.List = oDic(ComboBox2.Text)
    ComboBox3.Enabled = ComboBox3.ListCount > 1
    ComboBox3.ListIndex = 0
End Sub

Private Sub ComboBox3_Change()
    ComboBox4.Clear:  If ComboBox3.ListIndex < 0 Then Exit Sub
    ComboBox4.List = oDic(ComboBox2.Text & ComboBox3.Text)
    ComboBox4.Enabled = ComboBox4.ListCount > 1
    ComboBox4.ListIndex = 0
End Sub

Private Sub UserForm_Initialize()
  Const D = "¤"
    Dim V, R&, C$, K$
        V = Sheet1.ListObjects(1).DataBodyRange.Columns("A:S")
        Set oDic = CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(V)
            C = V(R, 2)
            K = C & V(R, 18)
        If oDic.Exists(V(R, 1)) Then
            If oDic.Exists(C) Then
                If oDic.Exists(K) Then
                    oDic(K) = Split(Join(oDic(K), D) & D & V(R, 19), D)
                Else
                    oDic(C) = Split(Join(oDic(C), D) & D & V(R, 18), D)
                    oDic.Add K, Array(V(R, 19))
                End If
            Else
                oDic(V(R, 1)) = Split(Join(oDic(V(R, 1)), D) & D & C, D)
                oDic.Add C, Array(V(R, 18))
                oDic.Add K, Array(V(R, 19))
            End If
        Else
            ComboBox1.AddItem V(R, 1)
            oDic.Add V(R, 1), Array(C)
            oDic.Add C, Array(V(R, 18))
            oDic.Add K, Array(V(R, 19))
        End If
    Next
        ComboBox1.Enabled = ComboBox1.ListCount > 1
        ComboBox1.ListIndex = 0
End Sub

Private Sub UserForm_Terminate()
    oDic.RemoveAll:  Set oDic = Nothing
End Sub

You should Like it !
​

 

[Yodelayheewho]

I spoke too soon in my last post. I may have fixed one thing, but I seem to have found another issue. So, I will definitely look closely at the alternate code you shared Marc and get back to you on how its going. Thank you!!!

 

[Marc L]

According to your initial post attachment once you have understood how works each post #19 combo box Change event​

see how they can be simplified :​

Spoiler

Private Sub ComboBoxChange(C%, K$)
        Dim N%
        For N = C + 1 To 4:  Controls("ComboBox" & N).Clear:  Next:  If Controls("ComboBox" & C).ListIndex < 0 Then Exit Sub
    With Controls("ComboBox" & C + 1)
        .List = oDic(K)
        .Enabled = .ListCount > 1
        .ListIndex = 0
    End With
End Sub

Private Sub ComboBox1_Change()
    ComboBoxChange 1, ComboBox1.Text
End Sub

Private Sub ComboBox2_Change()
    ComboBoxChange 2, ComboBox2.Text
End Sub

Private Sub ComboBox3_Change()
    ComboBoxChange 3, ComboBox2.Text & ComboBox3.Text
End Sub

 

[Yodelayheewho]

I reverted back to my small sample file, entered your updated code and it's working perfectly (attached)!
The big challenge ahead of me is integrating it into my large sample working file. However, before I do that, there are things in the code I've never seen before. Clearly, you are working at an advanced level.
My questions are in red. Green is what I think I know
Dim oDic As Object 'creates a variable to store dictionary objects stored as keywords and values. Why is this outside of the rest of the code?
Initialize Procedure
Const D = "¤" What does this line mean??
Dim V, R&, C$, K$ 'I understand without 'As' these variables default to Varient. However, what do the symbols mean?
For R = 1 To UBound(V) 'loops from the first row with data (1) to the last row with data
C = V(R, 2) 'C = loops through rows in column 2
K = C & V(R, 18) 'K = loops through rows in column 18
I believe the rest is checking if the keywords and values exists/not exists then take specific actions. Right?
ComboBoxChange Procedure
Private Sub ComboBoxChange (C%, K$) 'variants by default, but what do the symbols represent?

I can't thank you enough for your time and willingness to help me learn.

 

[Marc L]

Clearly, you are working at an advanced level.

Here I just use Excel / VBA basics (even if Dictionary is not a VBA feature but as it is well documented in VBA help) …​

​

Dim oDic As Object 'creates a variable to store dictionary objects stored as keywords and values. Why is this outside of the rest of the code?

oDic is a module global Object variable 'cause the same object is used within several procedures (Initialize, Change, Terminate) …​

Const D = "¤" What does this line mean??

As you can read in VBA help - a must see ! - Const just allocates a constant.​

D is the delimiter used within VBA functions Join & Split …​

Dim V, R&, C$, K$ 'I understand without 'As' these variables default to Varient. However, what do the symbols mean?

Without any declaration character yes it's a Variant, & and $ are specific declaration characters, an old school coding style BASIC legacy …​

Dim C$ is equal to Dim C As String as explained in String data type VBA help.​

​

During the execution in debug mode you can check each constant / variable data type in the VBE Locals window …​

C = V(R, 2) 'C = loops through rows in column 2

No, it just allocates any data type from the variable V to the String variable C.​

Mandatory 'cause a combo box data type is text only even if using its Value property​

so according to cell B3 its value is the number 10403 but once allocated to ComboBox1 it becomes a String​

but as the Dictionary makes the difference between a number like 10403 and a text like "10403"​

so the ComboBox1 value "10403" can never be found if the original number is stored within the Dictionary …​

K = C & V(R, 18) 'K = loops through rows in column 18

No, it creates the key according to column STID …​

I believe the rest is checking if the keywords and values exists/not exists then take specific actions. Right?

It's the cascading logic as all is calculated once in the Initialize event​

rather than each time in each combo box Change event like in post #2 …​

​

 

[Yodelayheewho]

VBA help

VBA Help? Is this a resource within this forum that I've missed?

VBE Locals window

Embarrassed to admit, that's a tool I've not taken advantage of. I will now! I noticed the "¤" = string. I assume you use that because of your past experience.

No, it just allocates any data type from the variable V to the String variable C.

Great explanation! Thank you!
Marc, you've helped me so much to increase my knowledge of VBA! I need to apply your code for the End User fields today and want to see if I can do this without needing help. I'm so very, very close to being able to use my latest iteration of the userform for 'real world' use.
Thanks again!

 

[Marc L]

The help integrated on VBE side like for example when the text cursor blinking on a VBA statement just hitting the F1 key …​

​