Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 52
  • Last Modified:

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.
0
Shums
Asked:
Shums
  • 6
  • 5
2 Solutions
 
ShumsAsst. Financial ControllerAuthor Commented:
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

0
 
Martin LissRetired ProgrammerCommented:
Can you supply a sample workbook?
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ShumsAsst. Financial ControllerAuthor Commented:
Ok I have created a Sample Workbook, just for this project, please find attached...
Populate-ComboBox-On-Dependent-Comb.xlsm
0
 
Martin LissRetired ProgrammerCommented:
OK, thanks.

What should happen if the user has only made a selection fromGroup or Activity and not both and they click InvoiceCombobox?

Do you want to prevent them from clicking that combobox if they haven't selected from the other two?
0
 
Martin LissRetired ProgrammerCommented:
The attached does not allow the user to open the InvoiceCombobox unless a selection is made from both of the other comboboxes. I believe I marked all the changes I made with 'new comments
28971290.xlsm
0
 
ShumsAsst. Financial ControllerAuthor Commented:
Thats Perfect.

Thanks so much Martin.
0
 
ShumsAsst. Financial ControllerAuthor Commented:
Do I need to remove all other codes which you haven't added comments?
0
 
Martin LissRetired ProgrammerCommented:
I'm not sure what you mean.
0
 
ShumsAsst. Financial ControllerAuthor Commented:
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

0
 
Martin LissRetired ProgrammerCommented:
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
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now