Solved

Populate Combobox Depending on other two Combobox

Posted on 2016-09-21
11
37 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 11

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 46

Expert Comment

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

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 46

Assisted Solution

by:Martin Liss
Martin Liss earned 500 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 46

Accepted Solution

by:
Martin Liss earned 500 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 11

Author Closing Comment

by:Shums
ID: 41811320
Thats Perfect.

Thanks so much Martin.
0
 
LVL 11

Author Comment

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

Expert Comment

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

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 46

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
EXCEL 2013 question. 4 25
Excel error  #DIV/0! 7 18
Merging spreadsheets 8 37
Msgbox tickler 10 23
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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