2

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.

BenMorel
  • 34,448
  • 50
  • 182
  • 322
user2759242
  • 73
  • 2
  • 2
  • 4

2 Answers2

3

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

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
  • Not quite working for me. Not sure why... This has been a less than cooperative project. I think I'm going to send the row to a worksheet and then add it to the other listbox from there. – user2759242 Sep 29 '13 at 05:22
  • What is not working? What error are you getting? Are you trying to copy a multicolumn row from a listbox to another? – Siddharth Rout Sep 29 '13 at 05:31
  • I think I see what I may have been doing wrong before. Unfortunately I'm not at work at the moment, but when I return I'll give this a shot and let you know how it goes. I've revamped this project quite a bit and it would be nice to be able to take care of what I was trying to do in this way now. – user2759242 Oct 25 '13 at 23:52
  • But this solution works only with ListBoxes with 10 columns or less. – Alsatian Apr 05 '16 at 11:20
  • @Alsatian: No that is not true :) – Siddharth Rout Apr 05 '16 at 14:02
  • You cannot use ListBox2.List(j, k) with k > 10. If you do that an error occures. – Alsatian Apr 05 '16 at 14:03
0

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
Blackrock41
  • 1
  • 1