Userform: Multiple Combobox Dynamic List Population

[Kaladin]

Hi all.

I have 5 comboboxes that pull the same list of data. I want all of the comboboxes to dynamically change which items are available in the list.

If a value is selected in a combobox, I don't want that value to be in the list of any of the other comboboxes. How can I do this?

Also, it would be nice if the other comboboxes did not lose their current selection when the other comboboxes have their list altered.

Any ideas?

 

[Andrew Poulsom]

Why do you need five ComboBoxes with the same list? Can't you use a MultiSelect ListBox?

 

[Kaladin]

Excellent question. It never even occurred to me. I like the aesthetic better with the comboboxes, but the list box is probably more user friendly.

I never use listboxes, how would I go about allowing the user to make only 5 selections in the listbox and then placing them in a sheet?

 

[Andrew Poulsom]

What's your current VBA code?

 

[Kaladin]

I was just populating my combobox, then trying to alter the others with change statements.

Dim r As Range
                Formation.ABox1.Clear
                    For Each r In Range("GroupA")
                        Formation.ABox1.AddItem r.Value
                    Next r

For each box to populate them.

The change code was:

Formation.ABox2.Clear
                    For Each r In Range("GroupA")
                        Select Case r.Value
                        
                            Case Is <> Formation.ABox1.Value, Formation.ABox3.Value, Formation.ABox4.Value, Formation.ABox5.Value, ""
                        
                                Formation.ABox2.AddItem r.Value
                                
                        End Select
                                            
                    Next r

For each box. Couldn't get them to work correctly, though.

 

[Andrew Poulsom]

No, mean what code is populating the worksheet?

 

[Kaladin]

Oh, I haven't done that yet. That seems simple enough. I just fooled around with the Listbox idea and got it to work, but I don't know how to limit the selections in the ListBox.

Here's what I have right now to populate the sheet with the listbox:

Dim I As Long
Dim J As Long
Dim arrItems()
    ReDim arrItems(0 To AListBox.ColumnCount - 1)
    For J = 0 To AListBox.ListCount - 1
        If AListBox.Selected(J) Then


            For I = 0 To AListBox.ColumnCount - 1
                arrItems(I) = AListBox.Column(I, J)
            Next I
            
            With Sheets("CombatData")
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Resize(, AListBox.ColumnCount).Value = arrItems
            End With
        End If
    Next J

To populate the Listbox I used:

Dim r As Range
                Formation.AListBox.Clear
                    For Each r In Range("TeamA")
                        Formation.AListBox.AddItem r.Value
                    Next r

 

[Andrew Poulsom]

Example:

Private Sub ListBox1_Change()
    Const MaxItems As Long = 5
    Dim i As Long
    Dim Count As Long
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) = True Then
                Count = Count + 1
                If Count > MaxItems Then
                    .Selected(i) = False
                End If
            End If
        Next i
    End With
End Sub