Cell update from listbox selections

[dnelson24]

I have a form (Form1) with a listbox on it (ListBox1). The listbox gets it rowsource from Groups(a defined name range with the worksheet). I have the listbox set to SelectMulti. All I want is when I select 2 or more items, and click Button1, for the values selected to be placed in a cell (D45) and have each value separated by a comma. Since I know very litte about VB this is probably a simple question for someone!

Thanks so much in advance.

David

 

[SkipVought]

hi,

Private Sub CommandButton1_Click()
    s = ""
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            s = s & ListBox1.Column(0, i) & ","
        End If
    Next
    s = Left(s, Len(s) - 1)
    [d1] = s
End Sub

Skip,
[sub]
[red]Be Advised![/red] Dyslexic poets...
write inverse! [/sub]

 

[dnelson24]

Thanks Skip...
don't get any errors, which is always good but how do I get this to put values into my cell? I would like the selected items to be placed into cell D45, comma seperated!

Thanks Again
David

 

[dnelson24]

oh i see, figured it out!
Thanks Again
David

 

[dnelson24]

Skip,
I get an error "Run-time error '5':
Invalid procedure call or argument" when I don't select a value from the listbox. Sometimes they may not need to select a value from this particular listbox. How can I allow that?
Thanks David

 

[SkipVought]

1. When yo uget the error message, hit Debug. It will show you WHERE the error is occurring.

In this case it had NOTHING to do with the listbox.

It is the STRING value s that has nothing in it.

Private Sub CommandButton1_Click()
    s = ""
    For i = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(i) Then
            s = s & ListBox1.Column(0, i) & ","
        End If
    Next[b][red]
    If s <> "" Then _[/red][/b]
        s = Left(s, Len(s) - 1)
    [d1] = s
End Sub

Skip,
[sub]
[red]Be Advised![/red] Dyslexic poets...
write inverse! [/sub]