Make 4 selections from 6 choices using ComboBoxes

[Bruce54]

Hi All,
This should be simple, but I can't see it.
I have 4 selections to make from a list of 6 choices in 3 separate groups.

[Structure]
lblTEAMS lblDOUBLES lblSINGLES
cboTeams1 cboDoubles1 cboSingles1
cboTeams2 cboDoubles2 cboSingles2
cboTeams3 cboDoubles3 cboSingles3
cboTeams4 cboDoubles4 cboSingles4
[/STRUCTURE]

When I select from the first combobox (cboTeams1), which is populated from a sheet range ("DATA",1,1 - "DATA",6,1), I want the 2nd Combobox (cboTeams2), to be populated by the SAME range MINUS cboTeams1.value. This should cascade down to the last cbo in that list (cboTeams4)

To illustrate:- each successive cbo has one less item (the previous chosen)
cboTeams1 cboTeams2 cboTeams3 cboTeams4
MEMBER1 MEMBER1 MEMBER1 MEMBER1
MEMBER2 [MEMBER2] MEMBER3 MEMBER3
MEMBER3 MEMBER3 MEMBER5 MEMBER5
[MEMBER4] MEMBER5 [MEMBER6]
MEMBER5 MEMBER6
MEMBER6

Hope this is clear enough for my Goal.

Obviously, the code can be duplicated across the 3 groups, as they have the same ListRowSource and will have different allocations of the resultant choices. So I only need help with the 1st groups combo-mechanics.

Thanks for reading, thanks in advance for helping.
Always appreciated
Bruce 54

 

[dmt32]

Hi,
try this

Private Sub CboTeams1_Change()
    PopulateComboBox Me.CboTeams1, Me.CboTeams2
End Sub
Private Sub CboTeams2_Change()
    PopulateComboBox Me.CboTeams2, Me.CboTeams3
End Sub
Private Sub CboTeams3_Change()
    PopulateComboBox Me.CboTeams3, Me.CboTeams4
End Sub


Sub PopulateComboBox(ByVal SelectionComboBox As Object, ByVal FillComboBox As Object)
    Dim arr() As String
    Dim Item As Variant
    Dim i As Integer
    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

If you need to use PopulateComboBox code with other userforms place it in a Standard module.

Dave

 

[Bruce54]

Hi DMT32, it works PERFECTLY, many, many thanks.
it's so good, I don't need to go any further. 100% success!
My aim was to keep half-inebriated Players from "fiddling"
and you've very neatly done so.
What more can I say?
You've earned my admiration

Bruce54

 

[dmt32]

Hi DMT32, it works PERFECTLY, many, many thanks.
it's so good, I don't need to go any further. 100% success!
My aim was to keep half-inebriated Players from "fiddling"
and you've very neatly done so.
What more can I say?
You've earned my admiration

Bruce54

Hi,
Glad my suggestion works well for you & many thanks for your kind & generous feedback, it is very much appreciated

Dave