Transferring selected items from Listbox to next available cell in workbook

Question

I've a Listbox1 and users select items to move into Listbox2. I need to move everything from Listbox2 into Excel.

The Userform should then close Me and another Userform should be displayed (either UF1, UF2 or UF3; a formula in G3 calculates which new form should be displayed).

Private Sub CommandButton2_Click()

    Dim i as integer
    Dim LastRow As Long

    LastRow = Sheets("EnteredData").Range("F" & Rows.Count).End(xlUp).Row
    
    If Me.ListBox2.ListCount = 0 Then
        MsgBox ("Please select at least one role")
    Else
        For i = 0 To ListBox2.ListCount - 1
            Sheets("EnteredData").Range("F" & LastRow).Offset(1, 0).Value = ListBox2.List(i)
            Me.ListBox2.RemoveItem i
        Next i
    End If
    Unload Me
    Sheets("EnteredData").Range("G3").Show
    
End Sub

The issues:

If Listbox2 contains at least two items, Run-time error 381: Could not get the List property. Invalid property array index on line Sheets("EnteredData").Range("F" & LastRow).Offset(1, 0).Value = ListBox2.List(i)
The 'next' Userform called in Sheets("EnteredData").Range("G3").Show does not display the required form. Unload Me works, but the new form (e.g. UF1) isn't shown.

Your code doesn't check if an item is selected before trying to transfer to the sheet? Is that right? Btw one ask per question please. — SJR
But apropos your second question, Show is not a method of the range object. — SJR
@SJR - apologies, I was hoping to finish this form in one post. I'll fix Q2 by some If statements. Regarding your clarification, I've got an IF statement which ensures something is included in Listbox2. I now need transfer everything from Listbox2 to my Excel sheet. — Marc

SJR SJR · Accepted Answer · 2020-05-26T12:09:17

Try this.

Because you are removing items as you go your loop breaks - whenever you delete anything you need to loop backwards.

That said, that would result in your items being transferred backwards.

To get round that we don't loop backwards (!), but remove all the items in one go at the end using Clear.

Lastly, but by no means least, you need to update your LastRow variable in the loop to avoid overwriting (though you could dispense with it altogether).

Private Sub CommandButton1_Click()

Dim i As Long
Dim LastRow As Long

For i = 0 To ListBox2.ListCount - 1
    LastRow = Sheets("EnteredData").Range("F" & Rows.Count).End(xlUp).Row
    Sheets("EnteredData").Range("F" & LastRow).Offset(1, 0).Value = ListBox2.List(i)
Next i

Me.ListBox2.Clear

End Sub

Transferring selected items from Listbox to next available cell in workbook

1 Answers