Populating multi column listbox

david francisco
david francisco used Ask the Experts™
on
I have a list box pulling from a work sheet in excel with two columns, and it is populating the first column, but I can't figure out how to make the second column populate as well.

    Dim rngName As Range
    Dim ws As Worksheet
    Dim i As Integer

    Set ws = Worksheets("Equipment")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then Me.ListBox2.AddItem ws.Cells(i, 1).Value
    Next i

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Equipment")

With Me.ListBox2
    .ColumnCount = 2
    .ColumnWidths = "60;60"
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If ws.Cells(i, 1).Value <> vbNullString Then
            .AddItem
            .List(i, 0) = the item for column 1
            .List(i, 1) = the item for column 2
        End If
    Next
End With

Open in new window

Author

Commented:
What do I put for "the item for column 1" and "the item for column 2"? or where do I bind them?
"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
I just realized that if you encounter a blank cell in your loop that you would have a problem so try this

Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer
Dim lngNextItem As Long

Set ws = Worksheets("Equipment")

With Me.ListBox2
    .ColumnCount = 2
    .ColumnWidths = "60;60"
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
        If ws.Cells(i, 1).Value <> vbNullString Then
            lngNextItem = lngNextItem + 1
            .AddItem
            .List(lngNextItem, 0) = ws.Cells(i, 1).Value
            .List(lngNextItem, 1) =  ws.Cells(i, 2).Value
        End If
    Next
End With

Open in new window

If that doesn't work for you them please attach your workbook.
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial