Listbox values from userform in one cell in a worksheet

[Doflamingo]

Hi all, I try to find the code that would allow me to put the values of a listbox from a userform in one cell of an excel sheet.

Below

[FONT=Arial]For b = 0 To ListBox2.ListCount - 1[/FONT]
[FONT=Arial]With Cells(5, 24)[/FONT]
[FONT=Arial]        .Value = ListBox2.List(b)[/FONT]
[FONT=Arial]End With[/FONT]
[FONT=Arial]Next[/FONT]

Or

[FONT=Arial]Dim arrItems()[/FONT]
[FONT=Arial]Dim cnt As Long[/FONT]
[FONT=Arial]Dim I As Long[/FONT]

[FONT=Arial]    For I = 0 To ListBox2.ListCount - 1[/FONT]
[FONT=Arial]        If ListBox2.Selected(I) Then[/FONT]
[FONT=Arial]            ReDim Preserve arrItems(cnt)[/FONT]
[FONT=Arial]            arrItems(cnt) = ListBox2.List(I)[/FONT]
[FONT=Arial]            cnt = cnt + 1[/FONT]
[FONT=Arial]        End If[/FONT]
[FONT=Arial]    Next I[/FONT]

[FONT=Arial]    If cnt > 0 Then[/FONT]
[FONT=Arial]        Cells(5, 24).Value = Join(arrItems, ",")[/FONT]
[FONT=Arial]    End If[/FONT]

But none of those codes work...

Any idea ?

 

[Domenic]

If you simply want to list all items from your listbox, try...

    Worksheets("Sheet1").Cells(5, 24).Value = Join(Application.Transpose(Me.ListBox2.List), ",") 'change the sheet name accordingly

If you only want to list the selected items from a multi-select listbox, try something like this...

Private Sub CommandButton1_Click()
    Dim selectedItems() As Variant
    Dim selectedItemCount As Long
    Dim i As Long
    selectedItemCount = 0
    With Me.ListBox2
        ReDim selectedItems(0 To .ListCount - 1)
        For i = 0 To .ListCount - 1
            If .Selected(i) Then
                selectedItems(selectedItemCount) = .List(i)
                selectedItemCount = selectedItemCount + 1
            End If
        Next i
    End With
    If selectedItemCount > 0 Then
        ReDim Preserve selectedItems(0 To selectedItemCount - 1)
        Worksheets("Sheet1").Cells(5, 24).Value = Join(selectedItems, ",")
    Else
        Worksheets("Sheet1").Cells(5, 24).Value = ""
        MsgBox "No items selected from listbox!", vbExclamation
    End If
End Sub

Hope this helps!

 

[Doflamingo]

Hello @Domenic

Many thanks for your reply

I don't know why when I applied that line of code you gave me

Worksheets("Sheet1").Cells(5, 3).Value = Join(Application.Transpose(Me.ListBox1.List), ",")

VBA displays ''run time error 13'' type mismatch

Any idea ?

 

[Domenic]

Does your listbox contain multiple columns? If so, which column do you want to return? And, can you confirm whether you want all items listed in the listbox or only the selected items?

 

[Doflamingo]

Hi @Domenic,

Sorry I should have precised it. There is only 1 column in my listbox. I would like that all the items in my listbox be integrated in a cell of a worksheet and be separated by ";"

 

[Domenic]

It looks like you've filled your listbox using the List property instead of RowSource, and the source range contains an error value. In this case, you'll need to loop through the list of items in the listbox, and check whether the value is an error. If not, it can be added to a string variable, which will be written to your worksheet at the end. Accordingly, try the following instead...

    Dim currentItem As Variant
    Dim listboxItems As String
    Dim i As Long
    
    listboxItems = ""
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            currentItem = .list(i)
            If Not IsError(currentItem) Then
                listboxItems = listboxItems & "," & .list(i)
            End If
        Next i
    End With
    
    listboxItems = Mid(listboxItems, 2)
    
    Worksheets("Sheet1").Cells(5, 3).Value = listboxItems

Does this help?

 

[Doflamingo]

Hello @Domenic

Many thanks for your help and time, that works perfectly

Yes sorry I should have precised it. The listbox is not filled by Rowsource but rather by an other code like

if optionbutton1 = true then listbox1.value = "wc"
if optionbutton2 = true then listbox1.value = "..."
if optionbutton3 = true then listbox5.value = "na"

So as you can see, I wanted the items of the listbox1 that are wc, ..., na be integrated in a specific cell of a worksheet.

You really helped me on that issue, thanks a lot @Domenic

Kind regards

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!