Excel VBA - Populating multicolumn userform listbox from array. No data when array has only 1 item

Question

i have the following function that im using to populate a userform listbox with data from an array:

Function PopulateListboxWithArray(lstbox As MSForms.ListBox, var As Variant)

With lstbox

    If Not IsEmpty(var) Then
    .Clear
        .list = Application.Transpose(var)
        .ListIndex = -1
    End If

End With

End Function

My listbox contains two columns with the following properties:

PROBLEM

The data in the array has an ID column and a lastname column. I dont want the user to see the ID column so ive set that column width to 0 in the form.

When i import data that has more than one row, the data shows up in the listbox as expected.

However, when the array only contains one row of data, the listbox shows up blank !

I have tried deleting the columnwidths in the image above and when i do so and reimport the one row of data, i get the ID and lastname stacked on top of one another. But ofcourse this is not the result i want.

I have even tried replacing .list = Application.Transpose(var) with .list = var to no avail.

What am i doing wrong here, or is there a better way to populate a listbox?

cheers

score 0 · Accepted Answer · edited May 23 '17 at 12:00

I have found the answer in this post:Adding item in listbox with multiple columns

I needed to use the .List property My function now looks like this:

Function PopulateListboxWithArray(lstbox As MSForms.ListBox, var As Variant)

With lstbox

    If Not IsEmpty(var) Then
            .Clear
        If UBound(var, 2) > 0 Then
            .list = Application.Transpose(var)
        Else
            .AddItem var(0, 0)
            .list(.ListCount - 1, 1) = var(1, 0)
        End If

    End If

End With

End Function

user3598756 · Answer 2 · 2016-09-15T11:29:34.103

edit to add more "background"

not so sure why you're using Application.Transpose()

    With lstbox
        If Not IsEmpty(var) Then
            .Clear
            If UBound(var, 1) = 1 Then
                .AddItem
                .List(0, 0) = var(1, 1)
                .List(0, 1) = var(1, 2)
            Else
'               .List = Application.Transpose(var)
                .List = var
            End If
            .ListIndex = -1
        End If
    End With

where I populated var in the following way:

Private Sub UserForm_Initialize()
    Dim var As Variant

    With Worksheets("LB") '<--| change "LB" to your actual sheet name
        var = .Range("B1", .Cells(.rows.Count, "A").End(xlUp)).Value '<--| populate var with columns "A:B" cells values from row 1 down to column "A" last non empty row
    End With
    With Me.ListBox1
        .ColumnCount = 2 '<--| set listbox columns count
        .ColumnWidths = "0;144" '<--| set listbox columns width
    End With
    PopulateListboxWithArray Me.ListBox1, var
End Sub

hi, thanks for responding. The transpose is needed when multiple rows of data are returned. i did try the .list = var but that did not work. cheers — Nick, Sep 15 '16 at 11:24
@Nick, you are welcome. I added some background as to how I populate `var` and have it working — user3598756, Sep 15 '16 at 11:30

Excel VBA - Populating multicolumn userform listbox from array. No data when array has only 1 item

2 Answers2