Excel Dependent Combo Boxes

           Excel Dependent Drop Down Lists


In this example, we will create a userform that contains dependent combo boxes and a textbox. We will use  this userform to enter the data  into cells as fastly.

The UserForm will be opened automatically when any cell in "Column A" is selected.


           The important point in this template ; by creating a dependency between combo boxes,  selectively choices can be limited. When the user selects Supplier from first drop-down list ,as a result, Category can be selected according to supplier from second drop-down list. Later ,based on selected category ,Product can be selected  from third drop-down list. Code of product is added in textbox as automatically.

Firstly, we need to create lists of combo boxes . For this we created lists in other sheet. Heads of lists : Supplier | Category | Product | Code

 Secondly ,we need to define names for lists. When lists are subject to updates, we can use a dynamic range instead. To create a dynamic range name for the Supplier list, do the following:

1️⃣ Click Define Name in the Defined Names group on the Formulas tab. (In Excel 2003, Choose Name from the Insert menu and choose Define)
2️⃣ Name the list Supplier.
3️⃣ Enter the following formula in the Refers To control :                                                             =OFFSET(Database!$A$2;0;0;COUNTA(Database!$A:$A)-1)
4️⃣ Click OK.

We repeated steps 1 through 4 to create dynamic lists for the other three lists:
Category: =OFFSET(Database!$B$2;0;0;COUNTA(Database!$B:$B)-1)
Product: =OFFSET(Database!$C$2,0,0,COUNTA(Database!$C:$C)-1)
Code: =OFFSET(Database!$D$2,0,0,COUNTA(Database!$D:$D)-1)

Later , we entered codes into VBA Editor  . In this template we used the Scripting.Dictionary object to create dependent drop down lists.
Private Sub UserForm_Initialize()
Supplier = Application.Transpose(Range("Supplier"))
Category = Application.Transpose(Range("Category"))
Product = Application.Transpose(Range("Product"))
Code = Application.Transpose(Range("Code"))
Set SD = CreateObject("Scripting.Dictionary")
For Each x In Supplier
SD(x) = ""
Next x
ComboBox1.List = SD.keys
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex = -1 And IsError(Application.Match(ComboBox1, Supplier, 0)) Then
Set SD = CreateObject("Scripting.Dictionary")
bul = ComboBox1 & "*"
For Each c In Supplier:
If c Like bul Then SD(c) = ""
Next c
ComboBox1.List = SD.keys
ComboBox1.DropDown
Else
Evn = ComboBox1
If Evn = "" Then Exit Sub
Set d2 = CreateObject("Scripting.Dictionary")
For i = LBound(Category) To UBound(Category)
If Supplier(i) = Evn Then d2(Category(i)) = ""
Next i
tablo2 = d2.keys
ComboBox2.List = tablo2
ComboBox2.SetFocus
If Val(Application.Version) > 10 Then SendKeys "{f4}"
ComboBox1.BackColor = &H80FFFF
End If
End Sub




3 comments: