none
Cascading (Dependent) Combo boxes in Excel Form using vba - Need Help

    Question

  • Hi, first I want to thank you in advance for helping me out.

    I have developed a user form in excel. I have 2 combo boxes. The first one is Manager and the second one is Employee. Upon form initialize I populate all Managers and Employees from excel tabs with the code below (this populates everyone).

    What I need help with, is I want to update the Employee combo box depending on what Manager is selected. So if Manager A is selected I only want to show the employees attached to Manager A, and If Manager B is selected I only want to show their employees, etc.

    The table that I'm pulling from is on the 'EmployeeRefTable' tab and the information looks like this:

    Manager Employee
    A 1
    A 2
    A 3
    A 4
    B 5
    B 6
    B 7
    B 8
    C 9
    C 10
    C 11
    C 12

        Set DataSheet = ThisWorkbook.Worksheets("ManagerRefTable")
        Set MyList = DataSheet.Range("A2:A50")
        Rw = 1
        cboManager.Clear
        While MyList.Cells(Rw, 1).Value <> ""
            cboManager.AddItem
            cboManager.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
            cboManager.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value
            Rw = Rw + 1
        Wend
       
        Set DataSheet = ThisWorkbook.Worksheets("EmployeeRefTable")
        Set MyList = DataSheet.Range("B2:B250")
        Rw = 1
        cboEmployee.Clear
        While MyList.Cells(Rw, 1).Value <> ""
            cboEmployee.AddItem
            cboEmployee.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
            cboEmployee.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value
            Rw = Rw + 1
        Wend

    Thank you again,
    Brian

    Monday, January 13, 2014 10:45 PM

Answers

  • Hi,

    I think the issue is related to the code below. You could not use Rw as the index of cboEmployee List. Rw is used to record the related row number of MyList to add to cboEmployee.

    cboEmployee.AddItem
             cboEmployee.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
             cboEmployee.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value

    I edit it and it works well. In my sample, I use "Ri" to represent the index of cboEmployee List.

    Private Sub cboManager_Change()
    Set DataSheet = ThisWorkbook.Worksheets("EmployeeRefTable")
         Set MyList = DataSheet.Range("B2:B13")
    
        Set MyList2 = DataSheet.Range("A2:A13")
    
        Rw = 1
        Ri = 0
         cboEmployee.Clear
         While MyList2.Cells(Rw, 1).Value <> ""
             If MyList2.Cells(Rw, 1).Value = cboManager.Value Then
            cboEmployee.AddItem
             cboEmployee.List(Ri, 0) = MyList.Cells(Rw, 1).Value
             cboEmployee.List(Ri, 1) = MyList.Cells(Rw, 2).Value
             Ri = Ri + 1
           End If
    
            Rw = Rw + 1
         Wend
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 20, 2014 12:48 PM
    Moderator

All replies

  • Hi Brian-

    First, you'll want to find the change event for the manager box. Double click the box, then in the VBE code window, you should see a drop-down in the upper right with any available events.

    In that change events, just repeat your employee load code, with a few small additions to look at the manager first: (aircode)

    Set DataSheet = ThisWorkbook.Worksheets("EmployeeRefTable")
        Set MyList1 = DataSheet.Range("B2:B250")

        Set MyList2 = DataSheet.Range("A2:A250")

        Rw = 1
        cboEmployee.Clear
        While MyList2.Cells(Rw, 1).Value <> ""
            If   MyList2.Cells(Rw, 1).Value = cboManager.value then

            cboEmployee.AddItem
            cboEmployee.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
            cboEmployee.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value

           end if

            Rw = Rw + 1
        Wend

    Monday, January 13, 2014 11:16 PM
  • Thank you Keith. This works great, but only if I select the first manager in the list. If I select any other manager, then I get "Run-time error '381': Could not set the List property. Invalid property array index."

    Do you know what could be causing this?

    Thanks,

    Brian


    Tuesday, January 14, 2014 10:10 PM
  • Hi,

    I think the issue is related to the code below. You could not use Rw as the index of cboEmployee List. Rw is used to record the related row number of MyList to add to cboEmployee.

    cboEmployee.AddItem
             cboEmployee.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value
             cboEmployee.List(Rw - 1, 1) = MyList.Cells(Rw, 2).Value

    I edit it and it works well. In my sample, I use "Ri" to represent the index of cboEmployee List.

    Private Sub cboManager_Change()
    Set DataSheet = ThisWorkbook.Worksheets("EmployeeRefTable")
         Set MyList = DataSheet.Range("B2:B13")
    
        Set MyList2 = DataSheet.Range("A2:A13")
    
        Rw = 1
        Ri = 0
         cboEmployee.Clear
         While MyList2.Cells(Rw, 1).Value <> ""
             If MyList2.Cells(Rw, 1).Value = cboManager.Value Then
            cboEmployee.AddItem
             cboEmployee.List(Ri, 0) = MyList.Cells(Rw, 1).Value
             cboEmployee.List(Ri, 1) = MyList.Cells(Rw, 2).Value
             Ri = Ri + 1
           End If
    
            Rw = Rw + 1
         Wend
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, January 20, 2014 12:48 PM
    Moderator