I've struggled trying to word this in a way that would let me find an answer with my usual google-fu, but here goes...
I have a Userform with a MultiSelect Listbox, which is used to select the team members for a project. The Listbox Items are added with a dynamic named range RowSource (in code, not a fixed property of the Listbox.)
Users have the option to select the "Add New" item, which launches a separate Userform to populate the new information for a new Team Member. This works fine, and the info is automatically entered into the source data sheet in the correct location - so the dynamic named range updates.
The issue I have is that I can't get the original Listbox to update correctly.
I would like the Listbox to:
Refresh the list to add the New Team Member (this works using the dynamic named range)
Retain the Selected items that were selected before the User clicked on "Add New" - I have tried to do this by creating an array from the Selected Items, then matching them against the 'refreshed' source.(this fails completely.)
And Select the New Team Member (this also fails...)
Private Sub TeamBox_Change() Dim i As Long, j As Long, k As Long Dim Teamlist() As String k = 0 For i = 0 To TeamBox.ListCount - 1 If TeamBox.Selected(i) = True And TeamBox.List(i) <> "Add New" Then k = k + 1 ReDim Preserve Teamlist(k) Teamlist(k) = TeamBox.List(i) ElseIf TeamBox.Selected(i) And TeamBox.List(i) = "Add New" Then AddTeam.Show k = k + 1 ReDim Preserve Teamlist(k) Teamlist(k) = AddTeam.Teamname TeamBox.Selected(i) = False 'Deselect "Add New" End If Next i 'Find new value, reload List of Values and Re-Select again. If AddTeam.NewTeamBox.Value <> "" Then 'Reload contents to TeamBox Me.TeamBox.Object.ListFillRange = "Teamteam" 'Update List with dynamic named range contents For j = 0 To TeamBox.ListCount - 1 For k = 0 To UBound(Teamlist) If TeamBox.List(j) = Teamlist(k) Then TeamBox.Selected(j) = True End If Next k Next j End If End Sub
Any suggestions greatly appreciated!