Copy - paste multiple selections in a listbox to a cell

[aishnadar]

Hi
I have a named range set in Sheet 1. I have inserted a List box in Sheet 2 and provided the named range in the input cell. But How do I save the selected values from this list box to a cell separated by ", "

Pls help.

 

[Domenic]

It looks like the listbox is a form control. If so, try...

[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()
    [COLOR=darkblue]Dim[/COLOR] sText [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    
    sText = ""
    [COLOR=darkblue]With[/COLOR] Worksheets("Sheet2").ListBoxes("List Box 1") [COLOR=#008000]'change the listbox name accordingly[/COLOR]
        [COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] .ListCount
            [COLOR=darkblue]If[/COLOR] .Selected(i) [COLOR=darkblue]Then[/COLOR]
                sText = sText & ", " & .List(i)
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    
    sText = Mid(sText, 3)
    
    Worksheets("Sheet2").Range("A2").Value = sText [COLOR=green]'change the sheet name and cell reference accordingly[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!