Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

asked on

Populating a multi-column listbox from another list box

I have two list boxes in an Excel User Form.  I want the user to select an item from ListBox1 and copy the data to ListBox2.

ListBox1 and ListBox2 have identical number of columns, 2 and one bound column.

When I had only one column of data, the command button to move the selected item from ListBox1 to ListBox2 worked:

Private Sub CommandButton3_Click()
    Dim i As Integer
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) = True Then ListBox2.AddItem ListBox1.List(i)
    Next i
End Sub

What do I need to change in the above VBA to move both columns?

Thank you

Avatar of Rgonzo1971


pls try

    For i = 0 To ListBox1.ListCount - 1
         If ListBox1.Selected(i) = True Then 
              ListBox2.AddItem ListBox1.List(i, 0)
              ListBox2.List(ListBox2.ListCount - 1, 1) = ListBox1.List(i, 1)
         End If
     Next i

Open in new window

Avatar of GPSPOW


I am getting an overflow error
at which line?
Avatar of GPSPOW


The error is:

Run-time error '6'

Rgonzo1971's code should work just fine. What line of code causes the error?

Do you know how to use Debug? If you don't, or even if you do, you may want to look at this article of mine.
Avatar of GPSPOW


I figured out that the overflow error is due to the ListBox table I am using.  It gets its information from a MS-Query that creates a table.

When I copy the column data to another sheet and use that data in my listbox, I do not get the overflow error.

Any thoughts on how I can correct the data in the Query Table so it does not cause the overflow error?
Can you show the code that you execute when you use the "ListBox table"? And in that code indicate the line that causes the problem?
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW


Thank you   Worked perfectly