User Form Drop down lists to apply to multiple combo boxes

[Nikijune]

Heeeeey guys

I'm hoping this is a quick simple one

I have a user form with a combo box that allows the user to select a team name.

The next combo box I want to populate with tasks based on the team selected in combo box 1. I also need to apply this same code to 5 more combo boxes. (The user will be from one team but may be supporting another team so we are adding blank 'rows' to the user form for them to select the work that they have done for another team.)

Here is the code I have so far. The combo box 1 works fine. How can I get combo box 2 to select different lists of tasks based on what the first combo box says??

'Create Option List of Team Names
    Dim ctrl As MSForms.Control

    For Each ctrl In Controls
            If TypeOf ctrl Is MSForms.ComboBox Then
            If ctrl.Name Like "TeamNameCombo*" Then ctrl.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")
    
        End If
    Next ctrl


'Create Option List of Team Names


    For Each ctrl In Controls
            If TypeOf ctrl Is MSForms.ComboBox Then
            If ctrl.Name Like "TaskCombo*" Then ctrl.List = Array("Refunds", "Write Offs", "Cancellations", "Modifications", "Indemnities", _
                "Clearances", "Partial Payments New (Spreadsheet)", "Partial Payments IP (CRM7 Queue)", "Right of Withdrawals (Spreadsheet)", _
                "Refund Exceptions", "PSO Report", "CSD Credit report (new and in progress)", "Refund FixesRefunds (CRM7 Queue)", _
                "Bank Report", "Deletion Report")
    
        End If
    Next ctrl

Thanks in advance

 

[dmt32]

Hi,
If you want to use a same code for other parts of your application either create a separate Sub or a function if a return value is required.

To create a dependent list you could create a function that contains a series of arrays & index these using your first combobox listindex property

As an example based on array in your post

 Private Sub TeamNameCombo_Change()    
With Me.TeamNameCombo
      If .ListIndex <> -1 Then Me.TaskCombo.List = Tasks(.ListIndex) Else Me.TaskCombo.Clear
    End With
End Sub


Private Sub UserForm_Initialize()
    Me.TeamNameCombo.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")
End Sub


Function Tasks()
    Tasks = Array(Array("Refunds", "Write Offs", "Cancellations", "Modifications", "Indemnities"), _
            Array("Clearances", "Partial Payments New (Spreadsheet)", "Partial Payments IP (CRM7 Queue)"), _
            Array("Right of Withdrawals (Spreadsheet)", "Refund Exceptions", "PSO Report"), _
            Array("CSD Credit report (new and in progress)", "Refund FixesRefunds (CRM7 Queue)"), _
            Array("Bank Report", "Deletion Report"))
    
End Function

You can see I placed your array in a function & divided it up in to lists that would match selection made in first combobox.

The function can be called by anyother combobox as required.

This is just an example, you will need to see if it can be adapted to me your specific project need.

Hope Helpful

Dave

 

[Nikijune]

Dave - Thank you so much

I have applied the code as per below, however, the lists do not appear in the second combo box. I'm not getting any errors or indication of why this is not working.

Private Sub UserForm_Initialize()

 'Create Option List of Team Names
     Me.TeamName1.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")
     Me.TeamName2.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")
     Me.TeamName3.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")
     Me.TeamName4.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")
     Me.TeamName5.List = Array("Golf", "Passat", "Polo", "Scirocco", "Touran")

End Sub

Private Sub TeamNameCombo_Change()

    With Me.TeamName1
          If .ListIndex <> -1 Then Me.Name24.List = Tasks(.ListIndex) Else Me.Name24.Clear
    End With
    
    With Me.TeamName2
          If .ListIndex <> -1 Then Me.Name25.List = Tasks(.ListIndex) Else Me.Name25.Clear
        End With
End Sub

Function Tasks()
    
 Tasks = Array(Array("Refunds", "Write Offs", "Cancellations", "Modifications", "Indemnities", "Clearances", "Partial Payments New (Spreadsheet)" _
                    , "Partial Payments IP (CRM7 Queue)", "Right of Withdrawals (Spreadsheet)", "Refund Exceptions", "PSO Report", _
                    "CSD Credit report (new and in progress)", "Refund FixesRefunds (CRM7 Queue)", "Bank Report", "Deletion Report"), _
               Array("VT - QueriesVT", "VT - Cancellations", "VT - New Request", "VT - Instruct Agent", "BCA - Collections", "VT - Check SAP", _
                    "VT - EOC", "HB - Queries", "HB - Cancellations", "HB - Instruct Agent", "HB - End Of Contract Invoices", _
                    "HB - End of Contract Mailbox", "HB - End of Contract Enquiries", "SR - Queries & Charges", _
                    "BCA - Referrals", "Team Mailbox", "Team Printing", "1Link Additions", "VT Replies Mailbox", "Reg changes"), _
               Array("BPID Merge ", "CRA - New requests", "CRA - Activities", "CRA - Emails", "CRA - Refinance/Handback/VT", "DDC", _
                    "Dashboard: ADDACS", "Dashboard: AUDDIS", "Dashboard: ARUDD", "Direct Debit Mandates", "NCI - New request", _
                    "NCI - Completing", "NCI - Adhoc issues/investigating", "NCI - Activity", "STC - New request", "STC - Activities", _
                    "SARS - Folder work", "SARS - £10 request", "SARS - £1 request", "SAD/No Trace ", "SARs - Call transcribing", _
                    "VT Handoff Sheet: clear", "VT Handoff Sheet: worked"), _
               Array("FCM: Main queue", "FCM: Email (SR created)", "FCM: Transfer", "FCM: Terminations", "FCM: Mileage ", "FCM: Post", _
                    "FCM: Shortfall Calls", "INC Accept Letters / Responders", "INC Reports ", "Refinance: Submitting", "Refinance: Issuing", _
                    "Refinance: Activating Contras", "Refinance: Activating Matured", "Refinance: Chasers", "Refinance: Post", _
                    "Refinance: Emails", "Refinance: More Info", "Refinance: Decline Calls", "RELOADS: Issuing", "RELOADS: Chasing", _
                    "RELOADS: Complex Query", "RELOADS: Submitting", "TRANS: Submitting Deceased", "TRANS: Submitting 1 Director", _
                    "TRANS: Submitting 2 Directors", "TRANS: Resubmitting", "TRANS: Issuing / Completing ", "TRANS: Issuing Deceased", _
                    "TRANS: More Info / Query", "TRANS: Declined/Closed/Diarised", "SIG Invoices", "MXMI Report"), _
               Array("General post - Queries", "Mailboxes", "Returned Post - Opening ", "Returned Post - Keeper Enq", "Returned Post - Updating", _
                    "Returned Post - Outbound Calls", "VT Mailbox", "PPI Mailbox", "PPI Scanning", "RCR Scanning", "RCR Printing", _
                    "RCR Ack Letters", "BPID Merge", "BPID Split", "CBP: Input/Merging", "Recorded", "Prepaid ", "Cheques", "Indexing", _
                    "Post Sorting", "Scanning", "Print Room Control", "Ack Spreadsheet""BCA / Annual Statement Audit", "Unarchiving"))
    

End Function

p.s - just to be really annoying I have changed the name of my combo boxes - sorry

 

[dmt32]

Hi,

Combobox codes need to be in their own change events

Private Sub TeamName1_Change()
    With Me.TeamName1
          If .ListIndex <> -1 Then Me.Name24.List = Tasks(.ListIndex) Else Me.Name24.Clear
    End With
End Sub


Private Sub TeamName2_Change()
        With Me.TeamName2
          If .ListIndex <> -1 Then Me.Name25.List = Tasks(.ListIndex) Else Me.Name25.Clear
        End With
End Sub

'etc

'etc

Dave

 

[Nikijune]

Super stuff Dave!! Thank you so much!!!

 

[dmt32]

Super stuff Dave!! Thank you so much!!!

Most welcome - glad I could help.

Dave