Populate multicolumn combobox from multiselect listbox

bendalazzi

New Member
Joined
May 11, 2014
Messages
7
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:
NameTitleAccreditation
Person 1Title 1Accred 1
Person 2Title 2Accred 2
Person 3Title 3Accred 3
Person 4Title 4Accred 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.

Code:
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? :confused: Ignore the Draftsperson and Reviewer code portions for now, just trying to get the Designer ComboBox to work for now.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

bendalazzi

New Member
Joined
May 11, 2014
Messages
7
Nevermind, done it again ... stupid me. Had the n value as part of the ComboBox List property. Doh! Obviously has to be the below.

Code:
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
 
Upvote 0

Forum statistics

Threads
1,195,838
Messages
6,011,886
Members
441,651
Latest member
drewe2000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top