We help IT Professionals succeed at work.

Populating a multi-column listbox from another list box

GPSPOW
GPSPOW asked
on
3,072 Views
1 Endorsement
Last Modified: 2014-11-19
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
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

Author

Commented:
I am getting an overflow error
CERTIFIED EXPERT
Top Expert 2016

Commented:
at which line?

Author

Commented:
The error is:

Run-time error '6'

Overflow
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.

Author

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?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
Grand Poobah
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you   Worked perfectly
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.