Populate multicolumn combobox from multiselect listbox

[bendalazzi]

I have a vba userform with a multiselect ListBox (DesignTeam) and 3 ComboBoxes (Designer, Draftsperson & Reviewer). The user needs to multiselect items from the DesignTeam ListBox and then select them as designer, draftsperson and/or reviewer. For example:

In DesignTeam Listbox:

Name	Title	Accreditation
Person 1	Title 1	Accred 1
Person 2	Title 2	Accred 2
Person 3	Title 3	Accred 3
Person 4	Title 4	Accred 4

<tbody>
</tbody>

and so on ...

In Designer, Draftsperson and Reviewer ComboBoxes, the 3 columns of the selected items in the ListBox appear for singular selection. The user could select 4 or more items from the listbox, but still only select 1 designer, draftsperson and reviewer.

My code at the moment is able to do the bulk of this, however it only works if the user selects items in the listbox in sequential order (e.g. 1, 2, 3, 4). It fails if the user selects Person 3, or selects Person 1 then Person 3. The error received is 'Could not set the List property. Invalid property array index'. On debug, I can see that the issue is that it doesn't know what row to put the second and third columns in to.

Private Sub DesignTeam_Change()Dim n As Long
Designer.ColumnCount = 3
Draftsperson.ColumnCount = 3
Reviewer.ColumnCount = 3
    Designer.Clear
    For n = 0 To DesignTeam.ListCount - 1
        If DesignTeam.Selected(n) = True Then
            If Designer.Value = "" Then
                Designer.AddItem
                Designer.List(n, 0) = DesignTeam.List(n, 0)
                Designer.List(n, 1) = DesignTeam.List(n, 1)
                Designer.List(n, 2) = DesignTeam.List(n, 2)
            Else
                Designer.Value = Designer.Value & vbCrLf & DesignTeam.List(n, 0)
            End If
        End If
    Next n
    Draftsperson.Clear
    For n = 0 To DesignTeam.ListCount - 1
        If DesignTeam.Selected(n) = True Then
            If Draftsperson.Value = "" Then
                Draftsperson.AddItem DesignTeam.List(n, 0)
                Draftsperson.Column(1, n) = DesignTeam.List(n, 1)
                Draftsperson.Column(2, n) = DesignTeam.List(n, 2)
            Else
                Draftsperson.Value = Draftsperson.Value & vbCrLf & DesignTeam.List(n, 0)
            End If
        End If
    Next n
    Reviewer.Clear
    For n = 0 To DesignTeam.ListCount - 1
        If DesignTeam.Selected(n) = True Then
            If Reviewer.Value = "" Then
                Reviewer.AddItem DesignTeam.List(n, 0)
                Reviewer.Column(1, n) = DesignTeam.List(n, 1)
                Reviewer.Column(2, n) = DesignTeam.List(n, 2)
            Else
                Reviewer.Value = Reviewer.Value & vbCrLf & DesignTeam.List(n, 0)
            End If
        End If
    Next n
End Sub

What am I missing? Ignore the Draftsperson and Reviewer code portions for now, just trying to get the Designer ComboBox to work for now.

 

[bendalazzi]

Nevermind, done it again ... stupid me. Had the n value as part of the ComboBox List property. Doh! Obviously has to be the below.

Private Sub DesignTeam_Change()Dim n As Long
Designer.ColumnCount = 4
Draftsperson.ColumnCount = 4
Reviewer.ColumnCount = 4
    Designer.Clear
    For n = 0 To DesignTeam.ListCount - 1
        If DesignTeam.Selected(n) = True Then
            If Designer.Value = "" Then
                Designer.AddItem
                Designer.List(Designer.ListCount - 1, 0) = DesignTeam.List(n, 0)
                Designer.List(Designer.ListCount - 1, 1) = DesignTeam.List(n, 1)
                Designer.List(Designer.ListCount - 1, 2) = DesignTeam.List(n, 2)
            Else
                Designer.Value = Designer.Value & vbCrLf & DesignTeam.List(n, 0)
            End If
        End If
    Next n
    Draftsperson.Clear
    For n = 0 To DesignTeam.ListCount - 1
        If DesignTeam.Selected(n) = True Then
            If Draftsperson.Value = "" Then
                Draftsperson.AddItem
                Draftsperson.List(Draftsperson.ListCount - 1, 0) = DesignTeam.List(n, 0)
                Draftsperson.List(Draftsperson.ListCount - 1, 1) = DesignTeam.List(n, 1)
                Draftsperson.List(Draftsperson.ListCount - 1, 2) = DesignTeam.List(n, 2)
            Else
                Draftsperson.Value = Draftsperson.Value & vbCrLf & DesignTeam.List(n, 0)
            End If
        End If
    Next n
    Reviewer.Clear
    For n = 0 To DesignTeam.ListCount - 1
        If DesignTeam.Selected(n) = True Then
            If Reviewer.Value = "" Then
                Reviewer.AddItem
                Reviewer.List(Reviewer.ListCount - 1, 0) = DesignTeam.List(n, 0)
                Reviewer.List(Reviewer.ListCount - 1, 1) = DesignTeam.List(n, 1)
                Reviewer.List(Reviewer.ListCount - 1, 2) = DesignTeam.List(n, 2)
            Else
                Reviewer.Value = Reviewer.Value & vbCrLf & DesignTeam.List(n, 0)
            End If
        End If
    Next n
End Sub