Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Populate Combobox Depending on other two Combobox

Posted on 2016-09-21
11
Medium Priority
?
49 Views
Last Modified: 2016-09-22
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
Comment
Question by:Shums
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 27

Author Comment

by:Shums
ID: 41808811
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41809631
Can you supply a sample workbook?
0
 
LVL 27

Author Comment

by:Shums
ID: 41810230
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Author Comment

by:Shums
ID: 41810398
Ok I have created a Sample Workbook, just for this project, please find attached...
Populate-ComboBox-On-Dependent-Comb.xlsm
0
 
LVL 49

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 41810885
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
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41811094
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
 
LVL 27

Author Closing Comment

by:Shums
ID: 41811320
Thats Perfect.

Thanks so much Martin.
0
 
LVL 27

Author Comment

by:Shums
ID: 41811322
Do I need to remove all other codes which you haven't added comments?
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41811334
I'm not sure what you mean.
0
 
LVL 27

Author Comment

by:Shums
ID: 41811346
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
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41811348
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

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question