Solved

Populate Combobox Depending on other two Combobox

Posted on 2016-09-21
11
30 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
  • 6
  • 5
11 Comments
 
LVL 1

Author Comment

by:Shums
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
Can you supply a sample workbook?
0
 
LVL 1

Author Comment

by:Shums
Comment Utility
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
 
LVL 1

Author Comment

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

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
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 1

Author Closing Comment

by:Shums
Comment Utility
Thats Perfect.

Thanks so much Martin.
0
 
LVL 1

Author Comment

by:Shums
Comment Utility
Do I need to remove all other codes which you haven't added comments?
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I'm not sure what you mean.
0
 
LVL 1

Author Comment

by:Shums
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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 Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
EXCEL 2010 7 39
IF Statement 3 23
Query output to Excel producing error 7 33
Excel  Worksheet Relationships 7 24
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now