CODEWITHSUNDEEP
vbaexcel
webhead
webhead

Reputation: 33

VBA Excel populate single cell with listbox items

How can I pass all the items in a listbox to a single cell with comma delimiter in VBA?

Here's my current code, where I pass each item to an array, then set the cell value to the array, but it doesn't work.

        Dim histReturn() As Long
        Dim j As Integer
        For j = 0 To Me.history_lbx.ListCount - 1
            ReDim Preserve histReturn(j)
        Dim x As Variant
        For Each x In histReturn
            Cells(i, 4).Value = Cells(i, 4).Value & x & ", "
        Next x

Upvotes: 0

Views: 4880

Answers (2)

chris neilsen
chris neilsen

Reputation: 53137

There is no need to loop at all. You can use Join to create the comma delimited list, like this

Sub Demo
    Dim rng as Range
    Set rng = Cells(1, 1)

    If history_lbx.ListCount = 0 Then
        rng = vbNullString
    Else
        rng = Join(Application.Transpose(history_lbx.List), ",")
    End If
End Sub

Upvotes: 3

user1220978
user1220978

Reputation:

If you want to use an array, you should not redim in a loop, but once and for all, since you know the dimension : ReDim histReturn(history.ListCount) for example. But your array never gets any value, so when your second loop try to find ListBox items in it, it can't.

Here is an idea, looping to get ListBox items, adding to a String, then putting the result in a Cell.

Dim S As String
If history_lbx.ListCount = 0 Then
    Cells(1, 1).Value = ""
Else
    S = history_lbx.List(0)
    For i = 2 To history_lbx.ListCount
        S = S & ", " & history_lbx.List(i - 1)
    Next i
    Cells(1, 1).Value = S
End If

Upvotes: 2

Related Questions