asked on

Populate Combobox Depending on other two Combobox

Good Day Experts,

I am trying to Populate Combobox depending on two other Combobox.

Till now I have below code, which works perfect if I have only one dependent Combobox.

Private Sub Invoice_Template_Change()
Dim RngTemplate As Range, RngType As Range
Dim RngList1 As Range, RngList2 As Range
Dim TemplateSelected As String, TypeSelected As String
Dim Ws1 As Worksheet
Dim LR1 As Long

InvoiceComboBox.Clear
'Check that Template has been selected
If Invoice_Template.ListIndex <> -1 Then
    TemplateSelected = Invoice_Template.value
    TypeSelected = Invoice_Type.value
    Set Ws1 = Worksheets("WBEntryDetails")
    LR1 = Ws1.Range("S" & Rows.Count).End(xlUp).row
    Set RngList1 = Ws1.Range("S3:S" & LR1)
    Set RngList2 = Ws1.Range("T3:T" & LR1)
    For Each RngTemplate In RngList1
        For Each RngType In RngList2
            If RngTemplate.value = TemplateSelected And RngType.value = TypeSelected Then
                InvoiceComboBox.AddItem RngTemplate.Offset(, 5) 'Column x
        End If
        Next RngType
    Next RngTemplate
End If

End Sub

Open in new window

If I change InvoiceTemplateComboBox & InvoiceTypeComboBox then InvoiceNumberComboBox must list the unique values as per my two ComboBox Selection.

Please assist.

Shums Faruk

ASKER

I tried below as well, but it doesn't populate

Private Sub Invoice_Template_Change()
Dim RngTemplate As Range, RngType As Range
Dim RngList1 As Range, RngList2 As Range
Dim TemplateSelected As String, TypeSelected As String
Dim Ws1 As Worksheet
Dim LR1 As Long

InvoiceComboBox.Clear
'Check that Template has been selected
If Invoice_Template.ListIndex <> -1 Then
    TemplateSelected = Invoice_Template.value
    TypeSelected = Invoice_Type.value
    Set Ws1 = Worksheets("WBEntryDetails")
    LR1 = Ws1.Range("S" & Rows.Count).End(xlUp).row
    Set RngList1 = Ws1.Range("S3:S" & LR1)
    Set RngList2 = Ws1.Range("T3:T" & LR1)
    Select Case Invoice_Template.value
    Case "A"

        With InvoiceComboBox
            For Each RngType In RngList2
                If RngType.value = TypeSelected Then
                    InvoiceComboBox.AddItem RngType.Offset(, 4) 'Column x
                End If
            Next RngType
        End With
        
    Case "B"
    
        With InvoiceComboBox
            For Each RngTemplate In RngList1
                If RngTemplate.value = TemplateSelected Then
                    InvoiceComboBox.AddItem RngTemplate.Offset(, 5) 'Column x
                End If
            Next RngTemplate
        End With
        
    End Select
End If
End Sub

Open in new window

Martin Liss

Can you supply a sample workbook?

Shums Faruk

ASKER

Thanks Martin for coming back.

Sorry I cannot provide sample workbook, as I am working with many modules and userforms.

After many researches, I found this link Using ComboBox Selections to Look though Multiple Columns on spreadsheet to match criteria and populate ListBox
working close to my requirement. I modified as per below code, but it populate just 1st item from Column X.

Private Sub UserForm_Initialize()
With Application
    .WindowState = xlMaximized
    
End With
With Me
    
    .Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
    .Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
End With
Dim Ws As Worksheet
Dim RR As Range, rCell As Range
Dim objDictionary As Object
Dim LastRow As Long
Set objDictionary = CreateObject("Scripting.Dictionary")

Set Ws = Worksheets("WBEntryDetails")
LastRow = Ws.Range("S" & Rows.Count).End(xlUp).row

With Ws
    Set RR = .Range("S3:S" & LastRow)
    With Me.Invoice_Template
        .Clear
        For Each rCell In RR
            If Not objDictionary.Exists(rCell.value) Then
                .AddItem rCell.value
                objDictionary.Add rCell.value, objDictionary.Count
            End If
        Next
    End With
    objDictionary.RemoveAll
    Set RR = .Range("T3:T" & LastRow)
    With Me.Invoice_Type
        .Clear
        For Each rCell In RR
            If Not objDictionary.Exists(rCell.value) Then
                .AddItem rCell.value
                objDictionary.Add rCell.value, objDictionary.Count
            End If
        Next
    End With
    objDictionary.RemoveAll
    Set objDictionary = Nothing
    With Me.InvoiceComboBox
        .Clear
    End With
End With

'Set Focus on InvoiceTemplate
Invoice_Template.SetFocus

End Sub

Open in new window

Private Sub GetCondStrandValue()
Dim iRow As Long
Dim strValue As String
Dim Ws As Worksheet
Dim LastRow As Long
Set Ws = Worksheets("WBEntryDetails")
LastRow = Ws.Range("X" & Rows.Count).End(xlUp).row
strValue = vbNullString
If Me.Invoice_Template.value = vbNullString Or Me.Invoice_Type.value = vbNullString Then Exit Sub

With Ws
    For iRow = 3 To LastRow
        If StrComp(.Cells(iRow, 19).value, Me.Invoice_Template.value, 1) = 0 And _
         StrComp(.Cells(iRow, 20).value, Me.Invoice_Type.value, 1) = 0 Then
            strValue = .Cells(iRow, 24).value
            Exit For
        End If
    Next
End With

If strValue = vbNullString Then Exit Sub
With Me.InvoiceComboBox
    'If you only want a single value in the listbox un-comment the .clear line
    'Otherwise, values will continue to be added
    '.Clear
    .AddItem strValue
    .value = strValue
    .SetFocus
End With
End Sub

Open in new window

Private Sub GetCondStrandValue_OnlyUniqueValues()
Dim iRow As Long
Dim strValue As String
Dim objDictionary As Object
Dim Ws As Worksheet
Dim LastRow As Long
Set Ws = Worksheets("WBEntryDetails")
LastRow = Ws.Range("X" & Rows.Count).End(xlUp).row
strValue = vbNullString
If Me.Invoice_Template.value = vbNullString Or Me.Invoice_Type.value = vbNullString Then Exit Sub

With Ws
    For iRow = 3 To LastRow
        If StrComp(.Cells(iRow, 19).value, Me.Invoice_Template.value, 1) = 0 And _
         StrComp(.Cells(iRow, 20).value, Me.Invoice_Type.value, 1) = 0 Then
            strValue = .Cells(iRow, 24).value
            Exit For
        End If
    Next
End With

If strValue = vbNullString Then Exit Sub
Set objDictionary = CreateObject("Scripting.Dictionary")

With Me.InvoiceComboBox
    For iRow = .ListCount - 1 To 0 Step -1
        If Not IsNull(.List(iRow)) Then
            If Not objDictionary.Exists(.List(iRow)) Then
                objDictionary.Add .List(iRow), objDictionary.Count
            Else
                .RemoveItem iRow
            End If
        End If
    Next
    If Not objDictionary.Exists(strValue) Then
        .AddItem strValue
        .value = strValue
        .SetFocus
    End If
End With
End Sub

Open in new window

Private Sub Invoice_Template_Change()
Call GetCondStrandValue
End Sub

Open in new window

Private Sub Invoice_Type_Change()
Call GetCondStrandValue
End Sub

Open in new window

I would like to populate the complete list of Invoices from Column X as per selection of InvoiceTemplate ComboBox & InvoiceType ComboBox.

Hope I am explaining it correctly.

Shums Faruk

ASKER

Ok I have created a Sample Workbook, just for this project, please find attached...
Populate-ComboBox-On-Dependent-Comb.xlsm

SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shums Faruk

ASKER

Thats Perfect.

Thanks so much Martin.

Shums Faruk

ASKER

Do I need to remove all other codes which you haven't added comments?

Martin Liss

I'm not sure what you mean.

Shums Faruk

ASKER

Martin,

I removed all GetCondStrandValue codes, just left as per below and it worked like a charm.

Private Sub FillInvoiceCombo()
'new Added sub
Dim Ws As Worksheet
Dim RR As Range, rCell As Range
Dim objDictionary As Object
Dim LastRow As Long
Set objDictionary = CreateObject("Scripting.Dictionary")

Set Ws = Worksheets("Data")
LastRow = Ws.Range("A" & Rows.Count).End(xlUp).Row

With Ws
    objDictionary.RemoveAll
    Set RR = .Range("A2:A" & LastRow)
    With Me.InvoiceComboBox
        .Clear
        For Each rCell In RR
            If rCell.Value = Invoice_Template.Text And rCell.Offset(0, 1).Value = Invoice_Type.Text Then
                If Not objDictionary.Exists(rCell.Offset(0, 2).Value) Then
                    .AddItem rCell.Offset(0, 2).Value
                    objDictionary.Add rCell.Offset(0, 2).Value, objDictionary.Count
                End If
            End If
        Next
        If .ListCount > 0 Then
        Else
            .AddItem "<No match>"
        End If
        .ListIndex = 0
    End With
End With

End Sub

Open in new window

Private Sub Invoice_Template_Change()
'new
If Invoice_Type.ListIndex > -1 Then
    InvoiceComboBox.Enabled = True
    FillInvoiceCombo
End If

End Sub

Open in new window

Private Sub Invoice_Type_Change()

'new
If Invoice_Template.ListIndex > -1 Then
    InvoiceComboBox.Enabled = True
    FillInvoiceCombo
End If

End Sub

Open in new window

Private Sub UserForm_Initialize()
With Application
    .WindowState = xlMaximized
    
End With
With Me
    
    .Top = Int(((Application.Height / 2) + Application.Top) - (.Height / 2))
    .Left = Int(((Application.Width / 2) + Application.Left) - (.Width / 2))
End With
Dim Ws As Worksheet
Dim RR As Range, rCell As Range
Dim objDictionary As Object
Dim LastRow As Long
Set objDictionary = CreateObject("Scripting.Dictionary")

Set Ws = Worksheets("Data")
LastRow = Ws.Range("A" & Rows.Count).End(xlUp).Row

With Ws
    Set RR = .Range("A2:A" & LastRow)
    With Me.Invoice_Template
        .Clear
        For Each rCell In RR
            If Not objDictionary.Exists(rCell.Value) Then
                .AddItem rCell.Value
                objDictionary.Add rCell.Value, objDictionary.Count
            End If
        Next
    End With
    objDictionary.RemoveAll
    Set RR = .Range("B3:B" & LastRow)
    With Me.Invoice_Type
        .Clear
        For Each rCell In RR
            If Not objDictionary.Exists(rCell.Value) Then
                .AddItem rCell.Value
                objDictionary.Add rCell.Value, objDictionary.Count
            End If
        Next
    End With
    'new
'    objDictionary.RemoveAll
'    Set objDictionary = Nothing
'    With Me.InvoiceComboBox
'        .Clear
'    End With
End With

'Set Focus on InvoiceTemplate
Invoice_Template.SetFocus

End Sub

Open in new window

Martin Liss

OK, I see.

You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015