Populate other combo boxes based on previous combo boxes selection

[Pookiemeister]

Currently on a user form, I have 6 combo boxes. The first combo box gets its values from a spreadsheet. The first combo box pulls from the code below. What I am wanting to do is to have the other combo boxes remove any name that was already chosen from the previous combo box. Is this possible? Thank you.

See code below to see how its populated.

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "Employees!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
End Sub

 

[mikerickson]

Code like this for each combobox

Private Sub ComboBox1_Change()
    Dim i As Long
    ComboBox2.Clear
    With ComboBox1
        For i = 0 to .ListCount -1
            If Not(.Selected(i)) then ComboBox2.AddItem .List(i)
        Next i
    End With
End Sub

 

[Pookiemeister]

Thank @mikerickson for the quick response. So when I ran the code above, I received a "Compile Error: Method or data member not found". The error highlights the ".Selected" (in bold font) below. Might there be something that I missed? Here's what I have shown below:

Private Sub ComboBox1_Change()
    Dim i As Long
  
    ComboBox2.Clear
    With ComboBox1
        For i = 0 To .ListCount - 1
            If Not ([B].[/B]Selected(i)) Then ComboBox2.AddItem .List(i)
        Next i
    End With
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "Employees!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
  
End Sub

 

[mikerickson]

That line should be as below and the sub should be put in the userform's code module.

If Not(.Selected(i)) then ComboBox2.AddItem .List(i)

 

[Pookiemeister]

That line should be as below and the sub should be put in the userform's code module.

If Not(.Selected(i)) then ComboBox2.AddItem .List(i)

The code below is what I have and I am still getting the same error message with ".Selected..." still highlighted in blue.

Private Sub ComboBox1_Change()
Dim i As Long

ComboBox2.Clear
With ComboBox1
For i = 0 To .ListCount - 1
If Not (.Selected(i)) Then ComboBox2.AddItem .List(i)
Next i
End With
End Sub

 

[dmt32]

Hi,
try following & see if does what you want

Private Sub ComboBox1_Change()
    PopulateComboBox Me.ComboBox1, Me.ComboBox2
End Sub
Private Sub ComboBox2_Change()
    PopulateComboBox Me.ComboBox2, Me.ComboBox3
End Sub
Private Sub ComboBox3_Change()
    PopulateComboBox Me.ComboBox3, Me.ComboBox4
End Sub
Private Sub ComboBox4_Change()
    PopulateComboBox Me.ComboBox4, Me.ComboBox5
End Sub
Private Sub ComboBox5_Change()
    PopulateComboBox Me.ComboBox5, Me.ComboBox6
End Sub

Sub PopulateComboBox(ByVal SelectionComboBox As Object, ByVal FillComboBox As Object)
    Dim arr() As String
    Dim Item As Variant
    Dim i As Long
    With SelectionComboBox
        ReDim arr(1 To .ListCount - 1)
        For Each Item In .List
            If Item <> .Text Then i = i + 1: arr(i) = Item
        Next Item
    End With
    With FillComboBox
        .RowSource = "": .List = arr
    End With
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.RowSource = "Employees!A2:A" & Range("A" & Rows.Count).End(xlUp).Row
End Sub

Dave

 

[Pookiemeister]

Thank you Dave. That did exactly what I needed it to do.

 

[dmt32]

Thank you Dave. That did exactly what I needed it to do.

Welcome glad we were able to assist

Dave

 

[Pookiemeister]

@dmt32 I just ran into an error message "Runtime error '9': Subscript out of range". It occurred when I filled in all the combo boxes and then had to go back into the second combo box to make a correction. How can this be fixed? I believe that this error could occur when any of the combo boxes could be selected to make a correction. Thank you.

 

[dmt32]

Hi,
try this update

Sub PopulateComboBox(ByVal SelectionComboBox As Object, ByVal FillComboBox As Object)
    Dim arr()   As String
    Dim Item    As Variant
    Dim i       As Long, index As Long
    
    For index = Mid(SelectionComboBox.Name, 9) + 1 To 6
        SelectionComboBox.Parent.Controls("Combobox" & index).Clear
    Next
    
    If Len(SelectionComboBox.Text) = 0 Then Exit Sub
    
    With SelectionComboBox
        ReDim arr(1 To .ListCount - 1)
        For Each Item In .List
            If Item <> .Text Then i = i + 1: arr(i) = Item
        Next Item
    End With
    With FillComboBox
        .RowSource = "": .List = arr
    End With
End Sub

Dave