VBA Combobox dependant on adjacent combobox selection

[Lefemmenikita]

Hi. The question is around a VBA userform:

- there are 5 (or more) comboboxes

- Combobox 1: Employee Name

- Combobox 2:Employee code: Populated based on lookup of value selected in combo box 1(i.e. employee code is tied to employee name and is a unique identifier)

- Combo box 3: Department: Dependent on combo box 1. i.e. each employee can select a department depending on how many depts are assigned to them

- Combo box 4: Travel reason': list of values which can be selected from. If 'other' is selected, this creates a free text field

- Combo box 5: General ledger code: dependent on combo box 3 & 4. i.e. this is based on a lookup of department & travel reason


I have saved a copy of what the raw data might look like here:

https://1drv.ms/x/s!AnDRSxsPRV2gomZNaNN9n1lzT6n9



Thanks

 

[mikerickson]

Regarding ComboBox4, a combo box includes a free text field, so none need be added. Just make sure that the MatchEntry property is set to fmMatchEntryNone and MatchRequired is False.

Code like this might work for you.
Note that both the Employee Name and ID combo boxes:
1) Are sorted.
2) Have a hidden column that contains the cell address from which the data came.

Dim DisableMyEvents As Boolean

Private Sub ComboBox1_Change()
    If DisableMyEvents Then Exit Sub
    MatchEmployeeID ComboBox1, ComboBox2
End Sub

Private Sub ComboBox2_Change()
    If DisableMyEvents Then Exit Sub
    MatchEmployeeID ComboBox2, ComboBox1
End Sub

Sub MatchEmployeeID(aBox As MSForms.ComboBox, bBox As MSForms.ComboBox)
    Dim i As Long, rangeAddress As String
    If DisableMyEvents Then Exit Sub
    With aBox
        If .ListIndex <> -1 Then
            rangeAddress = .List(.ListIndex, 1)
        End If
    End With
    DisableMyEvents = True
    With bBox
        For i = 0 To .ListCount - 1
            If .List(i, 1) = rangeAddress Then
                .ListIndex = i
                Exit For
            End If
        Next i
    End With
    FillDepartment Range(rangeAddress)
    ComboBox4.ListIndex = -1
    DisableMyEvents = False
End Sub

Sub FillDepartment(ByVal aRange As Range)
    Set aRange = aRange.EntireRow.Range("D1")
    ListBox3.Clear
    Do Until aRange.Value = vbNullString
        ListBox3.AddItem CStr(aRange.Value)
        Set aRange = aRange.Offset(0, 1)
    Loop
End Sub

Private Sub UserForm_Initialize()
    Dim oneCell As Range
    Dim i As Long
    
    Rem can be set at design time
    ComboBox1.ColumnCount = 2
    ComboBox1.ColumnWidths = ";0"
    ComboBox2.ColumnWidths = ";0"
    ComboBox2.ColumnCount = 2
    ComboBox1.Style = fmStyleDropDownList
    ComboBox4.MatchEntry = fmMatchEntryNone
    ComboBox4.MatchRequired = False
    Rem end design
    
    For Each oneCell In Range(Range("A1"), Range("A56636").End(xlUp))
        With ComboBox1
            For i = 0 To .ListCount - 1
                If CStr(oneCell.Value) < CStr(.List(i, 0)) Then Exit For
            Next i
            .AddItem oneCell.Value, i
            .List(i, 1) = oneCell.Address(, , , True)
        End With
        With ComboBox2
            For i = 0 To .ListCount - 1
                If CStr(oneCell.Offset(0, 1).Value) < CStr(.List(i, 0)) Then Exit For
            Next i
            .AddItem oneCell.Offset(0, 1).Value, i
            .List(i, 1) = oneCell.Address(, , , True)
        End With
    Next oneCell
    
    ComboBox4.List = Range("M1:M4").Value
End Sub

 

[Lefemmenikita]

Thanks so much Mikeerickson!

I will try this and let you know how I go

 

[Lefemmenikita]

Hi Mike

Thanks for the code and apologies for the delay in testing (I haven't worked on this for some time since I have been incredibly ill for the last month).

just looking at this now and am getting stuck on the part of the code which refers to

 If .ListIndex <> -1 Then
            rangeAddress = .List(.ListIndex, 1)

i am getting the error 'Cannot get the list property, invalid argument'.

i am guessing this relates to "2) Have a hidden column that contains the cell address from which the data came.".


How should I do that? Do I need to insert a blank column next to the employee code that returns the cell reference of the selection? e.g. If employee code 1010 is selected, return "A34" where the employee code is stored.


Thanks

 

[mikerickson]

Did you fill the list box with the code in my Intialize event? That should have set up the hidden column.

 

[Lefemmenikita]

I think I am way off on what I should be doing.


So far, I have created a userform in VBA and copied and pasted the code you had given me as the code for the form.

The employee ID and the employee names are sorted in order from smallest to largest (the code) and then by alphabetical order for the names.

This is what it looks like: