VBA Code - More than one Depended Combobox.

[RAKESH230583]

Hi Friends,

Case : Below table shows some product details, where in under column A we have only 2 product i.e. Product A & Product B.
Column B represent product Group & Last column C represent Grade of (Product and its group).

Excel Sheet 1.

[TABLE="width: 216"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Product[/TD]
[TD]Group[/TD]
[TD]Grade[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Product A[/TD]
[TD]Gold[/TD]
[TD]Grade I[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Product A[/TD]
[TD]Gold[/TD]
[TD]Grade II[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Product A[/TD]
[TD]Silver[/TD]
[TD]Grade I[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Product A[/TD]
[TD]Silver[/TD]
[TD]Grade II[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Product A[/TD]
[TD]Silver[/TD]
[TD]Grade III[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Product A[/TD]
[TD]Platinum[/TD]
[TD]Grade I[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Product B[/TD]
[TD]Gold[/TD]
[TD]Grade I[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Product B[/TD]
[TD]Gold[/TD]
[TD]Grade II[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Product B[/TD]
[TD]Gold[/TD]
[TD]Grade III[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Product B[/TD]
[TD]Gold[/TD]
[TD]Grade IV[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Product B[/TD]
[TD]Silver[/TD]
[TD]Grade I[/TD]
[/TR]
</tbody>[/TABLE]

I had created 3 ComboBoxes in Excel userform - But i am not able to get the desired drop down list. As, all three are inter related to each other.

Requirement Example:
Combobox1 : User must see only 2 Product (Product A & B)
Combobox2 : User must see only those Group which are available against that product (i.e Platinum must show only against Product A and not against Product B) - Post selecting product in Combobox1
Combobox3 : User must see only those Grade which are available against that product & Group (i.e. if user select product as "A" and group as "Platinum", then under combobox3 - User must see only 1 Grade i.e. "Grade I")
.
I hope i am clear with above example -

Kindly share your knowledge, for me to understand how to tackle above with vba code.

Thanks in advance.

 

[Fluff]

How about

Option Explicit
Private Dic As Object

Private Sub ComboBox1_AfterUpdate()
   ComboBox2.Clear
   ComboBox2.List = Dic(ComboBox1.Value).keys
End Sub

Private Sub ComboBox2_AfterUpdate()
   ComboBox3.Clear
   ComboBox3.List = Dic(ComboBox1.Value)(ComboBox2.Value).keys
End Sub

Private Sub UserForm_Initialize()
   
   Dim v1 As String, v2 As String, v3 As String
   Dim Cl As Range
   
   Set Dic = CreateObject("scripting.dictionary")
   Dic.comparemode = vbTextCompare
   
   With Sheets("[COLOR=#ff0000]pcode[/COLOR]")
      For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         v1 = Cl.Value: v2 = Cl.Offset(, 1).Value: v3 = Cl.Offset(, 2).Value
         If Not Dic.exists(v1) Then
            Dic.Add v1, CreateObject("scripting.dictionary")
            Dic(v1).Add v2, CreateObject("scripting.dictionary")
            Dic(v1)(v2).Add v3, Nothing
         ElseIf Not Dic(v1).exists(v2) Then
            Dic(v1).Add v2, CreateObject("scripting.dictionary")
            Dic(v1)(v2).Add v3, Nothing
         ElseIf Not Dic(v1)(v2).exists(v3) Then
            Dic(v1)(v2).Add v3, Nothing
         End If
      Next Cl
   End With
   ComboBox1.List = Dic.keys
End Sub

Change sheet name in red to suit

 

[RAKESH230583]

Hi Thanks,

With above given Codes i am able to view the details in combobox1, But not in Combobox2 and combobox3
Instead of Private Sub ComboBox2_AfterUpdate() - can we use Change() ?

 

[Fluff]

You can use Click rather than AfterUpdate

Private Sub ComboBox1_click()
   ComboBox2.Clear
   ComboBox2.List = Dic(ComboBox1.Value).keys
End Sub
Private Sub ComboBox2_click()
   ComboBox3.Clear
   ComboBox3.List = Dic(ComboBox1.Value)(ComboBox2.Value).keys
End Sub

but Change is a lot more complicated.
Are your comboboxs called Combobox2 & Combobox3?

 

[RAKESH230583]

Thanks - Works great with click
Tried and tested
You are the best !

 

[Fluff]

Glad to help & thanks for the feedback