Create Price Quote With Userform That Contains Cascading Drop-Down Lists

Excel VBA Cascading Combo Boxes


        In this study, we created a userform that automatically displayed to make it easier to enter data into the worksheet. The userform contains three dependent combo boxes( combobox in which the list depends on the selection made in another combobox),textbox and button.

        We used Excel VBA Dictionary to create dependent drop down lists containing unique values and sorted from A to Z. Thus, we got faster and more effective results. The power of this Excel VBA !
        Dictionary in VBA is a collection object .User can store all kinds of things in it ;  texts,numbers, dates, arrays, ranges, variables and objects.
One of Dictionary important chracteristics is the use of unique keys.
User can group all kinds of data in a Dictionary to get easy and quick access to them since they are temporarily stored in memory.
Our codes to populate Combobox1 with unique distinct values sorted from A to Z :
Dim a, b As Long, k As Variant
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
For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
        If ComboBox1.List(b) < ComboBox1.List(a) Then
k = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = k
       End If
  Next
  Next

         With the Combobox_Change method, interaction between drop-down lists is provided :
   
excel vba dependent drop down list

excel cascading drop down lists

excel cascading comboboxes


         When any cell is selected in column A, we have added the following codes into Worksheet_SelectionChange method of Offer sheet to load the userform:
If Not Intersect(Range("A13:A39"), Target) Is Nothing And Target.Count = 1 Then
      UserForm1.Left = Target.Left + 25
     UserForm1.Top = Target.Top + 20 - Cells(ActiveWindow.ScrollRow, 1).Top
      UserForm1.Show
      Else
      Exit Sub
  End If

excel price quote

1 comment:

  1. Sample file is not downloadable so please send me a sample file copy to my email

    ReplyDelete