Populate a single sheet with multiple entries from a drop down list

Martin,

Attached is a modified version of your sample file. I have made a lot of changes.
First of all, you'll notice the 'Rota' sheet now has a different layout. You can enter the date of the Week Commencing in cell B1, and the days of the week in B3:F3 will automatically update. They are formatted to show the day of the week as well as the date.

Secondly, I renamed sheet 'Data' to be 'Lists'. In B1 is the header "Names" and starting in cell B2 and going down without skipping any rows (so no blanks in the list) is the list of names. I also edited the named ranged "names". It is now named "list_Names" and is a dynamic named range that is defined with this formula:

=Lists!$B$2:INDEX(Lists!$B:$B,COUNTA(Lists!$B:$B))

The reason it uses column B is because column A is hidden. It is a reserved column for the userform so that it can sort the name lists as names get shuffled around between assigned and unassigned.


Next is where the VBA stuff starts. I used the worksheet_selectionchange event for sheet 'Rota'. The event will only trigger when a single cell withing B4:F6 is selected. Techincally its B4:F(last row), but the last row in this example is 6. The code used for the selectionchange event is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Application.EnableEvents = False
    On Error GoTo ReEnable
    
    If Target.Cells.Count = 1 And Not Intersect(Target, Me.Range("B4:F" & Me.Cells(Rows.Count, "A").End(xlUp).Row)) Is Nothing Then
        Load frm_AssignDuty
        frm_AssignDuty.Show
    End If
    
ReEnable:
    Application.EnableEvents = True
    
End Sub

What that code does is pull up the userform named frm_AssignDuty. That is a userform I created that contains 2 listboxes and some buttons. The left-hand listbox is the list of unassigned names. The right-hand listbox is the list of assigned names. So the form opens, you can choose whichever unassigned names you want and move them over to the assigned names and click OK, which will fill the selected cell with the list of names and close the form. You don't have to use the ">" and "<" buttons to move names over, you can also double-click on a name and it will get moved that way, so both options are available (double-clicking and using the buttons). Pressing cancel will close the form without making any changes to the cell. The list of unassigned names is based on the list of full names and then it removes any names that have already been assigned in the same column. For example, if in cell B4 you assign Name01 and Name03, then in cell B5 those two names will no longer be available because they have already been assigned. If you want to free a name up again, click on cell B4 and unassign the name.