Dependent Dropdown (VBA) Again!

[Robby19]

Good day to all of you VBA masters!

Dependent dropdowns in userform are the bane of my existence and make me very.....very sad. But they are super useful and I once again need help in writing the code for it.

So I have a Table laid out like below;

Table1
[TABLE="width: 500"]
<TBODY>[TR]
[TD]1a[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]1a[/TD]
[TD]aa[/TD]
[/TR]
[TR]
[TD]1a[/TD]
[TD]aaa[/TD]
[/TR]
[TR]
[TD]2b[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]2b[/TD]
[TD]bb[/TD]
[/TR]
[TR]
[TD]3c[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]3c[/TD]
[TD]cc[/TD]
[/TR]
[TR]
[TD]3c[/TD]
[TD]ccc[/TD]
[/TR]
[TR]
[TD]4d[/TD]
[TD]d[/TD]
[/TR]
</TBODY>[/TABLE]

I currently use the following code to populate the first textbox. This textbox will be one of the values from the above table in the first column.

Private Sub UserForm_Initialize()
    'Get Raid Name from Active Raid
    Me.Txt.Value = Cells(3, 2)
End Sub

So I need the dependent dropdown combobox to look at the Txt.Value and show the corresponding info. For example, if TxtValue = 3c, then the dropdown should populate c, cc, ccc. Can you help?

 

[MickG]

If you use 2 comboboxes, you can select from column "A" in Combobox1" and see the related column "B" data in "Combobox2".
Try the below in the Userform Module.

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
ComboBox2.List = Split(Dic(ComboBox1.Value), ", ")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]




Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        Dic(Dn.Value) = Dic(Dn.Value) & ", " & Dn.Offset(, 1).Value
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ComboBox1.List = Dic.keys
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Robby19]

Hm, doesn't seem to work right. So my range for the first combobox would be the ActiveWorksheet B3. This should automatically be populated. Combobox2 should be populated from column C in Table1 on a different worksheet.

For example, ActiveWorksheet B3 = 3c

Table1 of Sheet 1
[TABLE="width: 500"]
<TBODY>[TR]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1a
[/TD]
[TD]a
[/TD]
[/TR]
[TR]
[TD]1a
[/TD]
[TD]aa
[/TD]
[/TR]
[TR]
[TD]2b
[/TD]
[TD]b
[/TD]
[/TR]
[TR]
[TD]3c
[/TD]
[TD]c
[/TD]
[/TR]
[TR]
[TD]3c
[/TD]
[TD]cc
[/TD]
[/TR]
[TR]
[TD]3c
[/TD]
[TD]ccc
[/TD]
[/TR]
</TBODY>[/TABLE]

So the options for Combobox2 should be c, cc, ccc.

 

[Robby19]

Anyone help please!

 

[MickG]

If your data is in columns "B" and "C" then I should think combobox1 should have the unique data list from column "B" and any selection in combobox1 will result in combobox2 showing the related data from column C"
see altered code below :-

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
ComboBox2.List = Split(Dic(ComboBox1.Value), ", ")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not Dic.Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        Dic(Dn.Value) = Dic(Dn.Value) & ", " & Dn.Offset(, 1).Value
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
ComboBox1.List = Dic.keys
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Robby19]

Still doesn't seem to do what I need. I think the issue here is that the code is not referencing the Table. The first combobox pulls the correct data from the activeworksheet. However, the table that I need to reference back to is located on a different sheet.

 

[MickG]

Do you mean the data that should be in combobox2 is from another sheet.
i.e. The data shown as being in column "C", in your first post is actually in another sheet.

 

[Robby19]

This is correct. Combobox1 is populated off of the ActiveWorksheet. Combobox2 should pull its data from another worksheet based off of the value in combobox1.

 

[MickG]

What column in the active sheet is the data in, and what column is the other sheet is the corresponding data in , and can I assume that both sets of data are on the same rows in both sheets, and what row does the data start on.

 

[Robby19]

Combobox1 = ActiveWorksheet B3

Table (WorksheetTab), ListObject Table4, Column A has the lookup info for ActiveWorksheet B3

Table (WorksheetTab), ListObject Table 4, Column B has the lookup info to populate ComboBox2.

Example;
If ActiveWorksheet B3 = 3

Then the VBA should look at Table4, Column A and find all of the matches. Then Combobox2 should pull the corresponding data from Table4 from Column B.