Populate a Listbox based on multiple listboxes

Question

I am trying to put together a Userform where my users can populate a project team. I am trying to do this by having 2 Listboxes where the data is selected and then a third where the data is passed to.

Listbox1 "Role" is populated with the positions within each project, i.e. Project Lead, Project Manager, Lead Engineer etc.

Listbox2 "Name" is a list of people within my team. Any person in my team can fill any of the from listbox1.

I would like the user to select one item from each of the Listboxes and then press a > button to move these selections across to Listbox3 "Team". When the selected items have populated Listbox3 they are removed from Listbox1 and 2 so that person and role cannot be selected again.

The way I am doing this at present is by only using 2 Listboxes, one with only the name and the second is the populated team, i am missing the Role.

Private Sub BTN_MoveSelectedRight_Click()

    Dim iCtr As Long

    For iCtr = 0 To Me.ListBox1.ListCount - 1

        If Me.ListBox1.Selected(iCtr) = True Then

            Me.ListBox2.AddItem Me.ListBox1.List(iCtr)

        End If

    Next iCtr

    For iCtr = Me.ListBox1.ListCount - 1 To 0 Step -1

        If Me.ListBox1.Selected(iCtr) = True Then

            Me.ListBox1.RemoveItem iCtr

        End If

    Next iCtr

End Sub

Any ideas?

Thanks

Steve

Answer 1

Download the zipped example file at the following link.

https://1drv.ms/u/s!ArAXPS2RpafCoGKNnXGRs7yhVBfW?e=2Ho62x

When copying the items back to the originating list boxes then there is no way of knowing the original positions and therefore can only copy to end of existing lists. However, I have added some example code to sort the lists if you want it.

I have added a prefix number to the roles where the original array is created so that the roles can be sorted to a predetermined position in the list. The names in lstNames are simply sorted in alphabetical order.

Again, feel free to get back to me if further assistance is required.

Answer 2

I have uploaded a second example workbook to the following link.

https://1drv.ms/u/s!ArAXPS2RpafCoGFREm-d7ccEkFGc

I think that the coding is somewhat better for the following reasons.

• Identifies if either list is empty before any other processing
• Identifies if either list does not have any selections before further processing
• Removes the Role and Name from their respective list boxes immediately after copying to the combined Role and Name ListBox. (No need for a separate loop to find and remove).

I have also included example code to copy of ALL of the Role and Names to a worksheet plus another example to copy only  SELECTED Role and Names to a worksheet. Thought this might be helpful in demonstrating how to reference the columns of a ListBox.

Again, feel free to get back to me with any questions.

Answer 3

You mean from the data that is written to the worksheet you want to populate the Role and Name listbox from that data? 

PS. Added with Edit: Or do you mean that you want to remove an entry from the Role and Name and then add the Role and the Name back into the first 2 list boxes?

Answer 4

OzzieMac

This works great, thank you. Is there a way of passing the data from each column back to the relevant listbox?

Regards

Steve

Answer 5

I have assumed that the third list box is a 2 column list box to hold both the Role and the Name. I have uploaded a zipped copy of an example workbook to OneDrive at the following link. A bit too hard to explain all without an example.

https://1drv.ms/u/s!ArAXPS2RpafCoGBSG_OvxPti2LM7

Note that on Multi column list boxes that in properties column count for bound columns starts at 1 but in VBA column references commence with zero.

The dummy list box lists are from created arrays in the Userform Initialize event.

For a list of dummy names see the link on the worksheet.

Feel free to get back to me if any questions.