VBA listbox copy to listbox

user2759242 picture user2759242 · Sep 28, 2013 · Viewed 15.8k times · Source

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

Siddharth Rout picture Siddharth Rout · Sep 28, 2013

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.

enter image description here

and the properties of the listboxes are set as below

enter image description here

and this is how your Sheet1 looks like.

enter image description here

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

enter image description here