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

Glen
GPSPOWAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

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

Regards
1
GPSPOWAuthor Commented:
I am getting an overflow error
0
Rgonzo1971Commented:
at which line?
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

GPSPOWAuthor Commented:
The error is:

Run-time error '6'

Overflow
0
Martin LissOlder than dirtCommented:
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.
0
GPSPOWAuthor Commented:
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?
0
Martin LissOlder than dirtCommented:
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?
0
Rory ArchibaldCommented:
I suspect your Integer is the problem - try using Long instead.

Not for points, but I'd suggest a more generic approach rather than specifying the number of columns:

Private Sub CommandButton3_Click()
    Dim i                     As Long
    Dim j                     As Long

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

End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
Thank you   Worked perfectly
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.