Creating Automatic Price Quote With Userform That Contains Cascading Drop-Down Lists

[kadrleyn]

Data can be quickly entered into the price quote (or to any worksheet) with
the userform that contains dependent(cascading) drop-down lists.
The user form automatically opens when the cell is selected in column A in the price quote.
The data selected from the drop-down lists on userform is entered into the worksheet.

 

[jindon]

See if this is how you wanted.

Option Explicit
Private dic As Object

Private Sub UserForm_Initialize()
    Dim a, i As Long
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("database").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 1)) Then
            Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
            dic(a(i, 1)).CompareMode = 1
        End If
        If Not dic(a(i, 1)).exists(a(i, 2)) Then
            Set dic(a(i, 1))(a(i, 2)) = CreateObject("Scripting.Dictionary")
            dic(a(i, 1))(a(i, 2)).CompareMode = 1
        End If
        dic(a(i, 1))(a(i, 2))(a(i, 3)) = a(i, 4)
    Next
    ComboBox1.List = dic.keys
   End Sub

Private Sub ComboBox1_Change()
    Dim i As Long
    For i = 2 To 3: Me("combobox" & i).Clear: Next
    Me.TextBox1.Value = vbNullString
    If Me.ComboBox1.ListIndex > -1 Then
        Me.ComboBox2.List = dic(Me.ComboBox1.Value).keys
        Me.ComboBox2.SetFocus
        If Val(Application.Version) > 10 Then SendKeys "{f4}"
        Me.ComboBox1.BackColor = &H80FFFF
    End If
End Sub

Private Sub ComboBox2_Change()
    Me.ComboBox3.Clear: Me.TextBox1.Value = vbNullString
    If (Me.ComboBox1.ListIndex > -1) * (Me.ComboBox2.ListIndex > -1) Then
        Me.ComboBox3.List = dic(Me.ComboBox1.Value)(Me.ComboBox2.Value).keys
        Me.ComboBox3.SetFocus
        If Val(Application.Version) > 10 Then SendKeys "{f4}"
        Me.ComboBox2.BackColor = &H80FFFF
    End If
End Sub

Private Sub ComboBox3_Change()
    Me.TextBox1.Value = vbNullString
    If (Me.ComboBox1.ListIndex > -1) * (Me.ComboBox2.ListIndex > -1) * (Me.ComboBox3.ListIndex > -1) Then
        Me.TextBox1.Value = dic(Me.ComboBox1.Value)(Me.ComboBox2.Value)(Me.ComboBox3.Value)
        Me.TextBox1.SetFocus
        If Val(Application.Version) > 10 Then SendKeys "{f4}"
        Me.ComboBox3.BackColor = &H80FFFF
    End If
End Sub

Private Sub CommandButton1_Click()
    If ComboBox1 <> "" And ComboBox2 <> "" Then
        ActiveCell = UCase(ComboBox1)
        ActiveCell.Offset(, 2) = ComboBox2
        ActiveCell.Offset(, 1) = ComboBox3
        ActiveCell.Offset(, 4) = TextBox1
        ActiveCell.Offset(, 4) = ActiveCell.Offset(, 4) * 1
        Unload Me
    Else
        MsgBox "Eksik!"
    End If
End Sub