VBA listbox copy to listbox

Question

Ok, this seems nuts and I've been researching it for a couple of hours but I can't find anything that works. This post is going to be pretty devoid of code, but I'm going to explain very quickly exactly what I'm trying to do.

So I have a listbox that I've successfully populated and it works just fine. At some point as directed by the user, the user will select a row from the listbox, call it RecordBox, Review some information, maybe add some, and then click a "save" command button. Upon Clicking this save button I'd like to copy the selected row from RecordBox to the second listbox. Call it DetailsBox I suppose.

I either need a way to take data displayed in the form in the form of captions, combobox entries, and text box entries, add a row to "DetailsBox" and copy the information to the particular columns of that row, or I need to simply copy the selected row from RecordBox to DetailsBox.

Anyway, if some of the code would be helpful, just ask, but there really isnt any aside from the command button click event.

I hope that would be enough information.

Answer 1

It's as simple as

ListBox2.AddItem ListBox1.List(ListBox1.ListIndex)

FOLLOWUP (From Comments)

I think I'm going to send the row to a worksheet and then add it to the other listbox from there.

I believe you are using a multicolumn listbox. In that case the above code will add only the first column to the 2nd listbox. You need to iterate through the rest of the columns to add the selected row from Listbox1.

Let's say your userform looks like this. I created a small sample for you.

and the properties of the listboxes are set as below

and this is how your Sheet1 looks like.

Now put use this code in the Userform.

Private Sub UserForm_Initialize()
    '~~> Adding Sample Data to listbox 1
    ListBox1.List = ThisWorkbook.Sheets(1).Range("A1:E3").Value
End Sub

Private Sub CommandButton1_Click()
    Dim iIndex
    Dim i As Long, j As Long, k As Long

    With ListBox1
        i = .ListIndex

        ListBox2.AddItem .List(i, 0), 0

        j = ListBox2.ListCount - 1

        For k = 1 To .ColumnCount - 1
            ListBox2.List(j, k) = .List(i, k)
        Next k
    End With
End Sub

When you click select an item in the Listbox1 and press the command button, you will notice that the selected row from Listbox1 is successfully copied to Listbox2

Answer 2

For any one looking at sending items from one list box to another using a loop and multi select. Heres some code that might help. Youll need to set both listboxes the properties to Mulitselect. 1-fmMultiSelectMulti. Then use the same settings/setup as Siddharth Rout posted above.

 Private Sub CommandButton1_Click()
Dim iIndex
Dim i As Long, j As Long, k As Long
ListBox2.Clear
For i = 0 To 2' loop 3 times for each row in listbox1.
   If ListBox1.Selected(i) = True Then 'Get the first selected Row index number.

   ListBox2.AddItem ListBox1.List(i, 0) 'Gets the first item from listbox1 and puts it in listbox2.
        j = ListBox2.ListCount - 1 ' counts all items in listbox2. which is one item.

        For k = 1 To ListBox1.ColumnCount - 1 'Count the columns listbox1.Now that the first item is in listbox2 _
        move over one column & copy the next value to listbox2. loop 4 more times for 4th entry of row one.
            ListBox2.List(j, k) = ListBox1.List(i, k)
         Next k
   End If
 Next i
End Sub