VBA Excel populate single cell with listbox items

Question

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

chris neilsen · Accepted Answer · 2014-03-30 01:26:51Z

3

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

edited Mar 30, 2014 at 1:26

answered Mar 29, 2014 at 6:57

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

webhead Over a year ago

this line gives a type mismatch error: rng = Join(Application.Transpose(history_lbx), ",")

chris neilsen Over a year ago

Editied answer. Typo, missing reference to List in Application.Transpose(history_lbx.List)

user1220978 · Accepted Answer · 2014-03-29 05:48:43Z

2

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

answered Mar 29, 2014 at 5:48

user1220978

Collectives™ on Stack Overflow

VBA Excel populate single cell with listbox items

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related