Kapat

Create Dynamic Dependent Drop Down Lists On Userform

userform dependent comboboxes

excel  listbox context menu Excel VBA Dependent
Drop Down Lists

We have a Database sheet with 4 columns consisting of supplier, category, product, price columns. To the other sheet (Sample sheet), we want to get the price information of the product we selected according to the supplier from this Database sheet.In the database, there are categories for each supplier ,products separated by these categories.
It should be a tool where we can easily see the categories belonging to the supplier and the products belonging to this category and the price of this product.

For this purpose, we created a userform, we added drop-down lists for each of them to list suppliers, categories and products to the userform, added a textbox control for the price.
When a supplier is selected from the first drop-down list, the second drop-down list should automatically list the categories belonging to this supplier, and the products belonging to this category in the third drop-down list.
These drop-down lists (combo boxes) are called dependent drop down lists(cascading drop down lists). excel dependent drop down lists

excel dependent drop down lists on userform

excel dependent comboboxes To create dynamic dependent drop-down lists in Excel, we first need to create dynamic range names. We created 4 dynamic range names based on the column headings of Database sheet : Supplier | Category | Product | Price
1️⃣ Supplier name refers to : =OFFSET(Database!$A$2,0,0,COUNTA(Database!$A:$A)-1)
2️⃣ Category name refers to : =OFFSET(Database!$B$2,0,0,COUNTA(Database!$B:$B)-1)
3️⃣ Product name refers to : =OFFSET(Database!$C$2,0,0,COUNTA(Database!$C:$C)-1)
4️⃣ Price name refers to : =OFFSET(Database!$D$2,0,0,COUNTA(Database!$D:$D)-1)

vba scripting dictionary We defined the names we created as array in the Userform_Initialize procedure and created the items to be listed in combobox1 using the Dictionary object.

Private Sub UserForm_Initialize()
Dim k As Byte, x As Variant
Me.BackColor = 15658720
For k = 1 To 4
Controls("Frame" & k).BackColor = 15658720
Next
 Supplier = Application.Transpose(Range("Supplier"))
 Category = Application.Transpose(Range("Category"))
 Product = Application.Transpose(Range("Product"))
 Price = Application.Transpose(Range("Price"))
 Set SD = CreateObject("Scripting.Dictionary")
  For Each x In Supplier
      SD(x) = ""
 Next x
  ComboBox1.List = SD.keys
 End Sub

vba cascading comboboxes We have added the necessary VBA codes to Combobox_Change procedures for to run combo boxes as dependent .

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.Clear
        ComboBox2.List = tablo2
        ComboBox2.SetFocus
        If Val(Application.Version) > 10 Then SendKeys "{f4}"
        ComboBox1.BackColor = &HC0FFFF
    End If
    i = Empty
    Set d2 = Nothing
End Sub

Private Sub ComboBox2_Change()
    If ComboBox1 <> "" Then
        If ComboBox2.ListIndex = -1 And IsError(Application.Match(ComboBox2, Category, 0)) Then
            Set SD = CreateObject("Scripting.Dictionary")
            bul = UCase(ComboBox2) & "*"
            For Each c In tablo2
                If UCase(c) Like bul Then SD(c) = ""
            Next c
            ComboBox2.List = SD.keys
            ComboBox2.DropDown
                Else
            Set d3 = CreateObject("Scripting.Dictionary")
            If ComboBox1 = "" Or ComboBox2 = "" Then Exit Sub
                For i = LBound(Product) To UBound(Product)
                If Supplier(i) = ComboBox1 And Category(i) = ComboBox2 Then
                d3(Product(i)) = ""
                End If
            Next i
            tablo3 = d3.keys
            ComboBox3.Clear
            ComboBox3.List = tablo3
            ComboBox3.SetFocus
            If Val(Application.Version) > 10 Then SendKeys "{f4}"
        End If
        ComboBox2.BackColor = &HC0FFFF
     End If
     i = Empty
     Set d3 = Nothing
End Sub

Private Sub ComboBox3_Change()
    If ComboBox1 <> "" And ComboBox2 <> "" And ComboBox3 <> "" Then
        If ComboBox3.ListIndex = -1 And IsError(Application.Match(ComboBox3, Product, 0)) Then
            Set SD = CreateObject("Scripting.Dictionary")
            bul = UCase(ComboBox3) & "*"
            For Each c In tablo3
                If c Like bul Then SD(c) = ""
            Next c
            ComboBox3.List = SD.keys
            ComboBox3.DropDown
               Else
            For i = LBound(Product) To UBound(Product)
                If Supplier(i) = ComboBox1 And Category(i) = ComboBox2 And Product(i) = ComboBox3 Then
                   TextBox1.Value = Price(i)
                               
                End If
            Next i
         End If
         ComboBox3.BackColor = &HC0FFFF
     End If
     i = Empty
End Sub

excel dynamic dependent drop down lists When any new record(new row) is added to the Database sheet, it will automatically be listed in the dependent drop-down lists on userform.This is the purpose of being dynamic dependent combo boxes.

vba Worksheet SelectionChange We have added the following codes to the Worksheet_SelectionChange procedure to display the userform when any cell is selected in column A on Sample sheet. vba show userform when cell is selected

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Target.Row = 1 Then Exit Sub
     If Not Intersect(Range("A:A"), Target) Is Nothing And Target.Count = 1 Then
      UserForm1.Left = Target.Left + 25
    UserForm1.Top = Target.Top + 30 - Cells(ActiveWindow.ScrollRow, 1).Top
    UserForm1.Show
    End If
End Sub
excel show userform when cell selected

vba delete blank row In order to avoid any problems while retrieving data from Database sheet, if there are blank rows between cells, it should be deleted. For this purpose, we have added the following codes that run when leaving this sheet :

Private Sub Worksheet_Deactivate()
Dim LastRow As Long
LastRow = Sheets("Database").Range("A" & Sheets("Database").Rows.Count).End(xlUp).Row
With Sheets("Database").Range("A2:A" & LastRow)
    If WorksheetFunction.CountBlank(.Cells) > 0 Then
        .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End If
End With
End Sub

📥 Sample workbook can be downloaded here


Price Quote With Userform That Contains Dependent Drop-Down Lists

Products can be quickly and easily entered into the price quote template using the userform that contains dependent(cascading) drop-down lists.
The userform is displayed when clicked any cell in the range A13: A39 on the “Offer” sheet .
Product information is fetched from the “Database” sheet with dependent drop-down lists.

📥 Price quote template can be downloaded here