Multiple selected Listbox values to cell values

Question

I have the following code on a button that takes the selected range in a Listbox on a Userform and pastes the value into Sheets("VBA_Data").Range("AY1").

This only works with one selection, and if you select multiple selections in the Listbox it will only add the first value to cell AY1.

I'd like to modify the code so that it will add all selected values in the Listbox, one to each cell in column AY beginning at AY1. The range of the Listbox is variable, so this could be for any amount of selections.

Private Sub CommandButtonRemoveSelectedProjects_Click()

Dim selectioncount As Long
selectioncount = ListBoxProjectsToRemove.Selected(i)

Set TxtRng = ActiveWorkbook.Sheets("VBA_Data").Range("AY1")

For i = 0 To ListBoxProjectsToRemove.ListCount - 1
If ListBoxProjectsToRemove.Selected(i) Then
    SelectedItemText = ListBoxProjectsToRemove.List(i)
End If
Next i

TxtRng.Value = SelectedItemText

End Sub

Answer 1

You could simple change the target range to the next cell.

Set TxtRng = ActiveWorkbook.Sheets("VBA_Data").Range("AY1")

For i = 0 To ListBoxProjectsToRemove.ListCount - 1
    If ListBoxProjectsToRemove.Selected(i) Then
        TxtRng.Value = ListBoxProjectsToRemove.List(i)
        Set TxtRng = TxtRng.Offset(1)
    End If
Next i