Multiple Selections from Listbox into an Array, populated by a VBA form

[championem31]

So I have created a form for people to fill out, and I want them to be able to pick several options from a listbox. Then, when they save the form, I want the options they selected to populate in the next available row as an array. Here's what I have:

Private Sub btnsave_click()


Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("13-14")




'find first row in database


With ws
irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


.Range("A" & irow) = TypePresEvent.Value
.Range("B" & irow) = Topic.Value
.Range("D" & irow) = Class.Value
.Range("E" & irow) = Location.Value
.Range("F" & irow) = Attendance.Value
.Range("G" & irow) = Items.Value
.Range("H" & irow) = Notes.Value








End With
TypePresEvent.Value = ""
Topic.Value = ""
Class.Value = ""
Location.Value = ""
Attendance.Value = ""
Notes.Value = ""






Dim arrValues()
If Presenters.ListIndex <> -1 Then
For I = 0 To Presenters.ListCount - 1
If Presenters.Selected(I) Then
ReDim Preserve arrValues(X)
arrValues(X) = Presenters.List(I)
X = X + 1

End If

End If
Next I



Range("C65536").End(xlUp).Offset(1, 0).Select = Join(arrValues, ",")
With ws
End With
Presenters.Value = ""


End Sub



Needless to say, I keep getting errors (BLOCK IF END IF crap). Please help! Also, if you know a way to clear the selections off of the listbox after its submitted, that would be helpful too.

I'm self-taught at this, so please be nice.

 

[Norie]

Move Next I above the 2nd End If.

Actually you can remove the If that checks ListIndex, it's kind of irrelevant with a multi select listbox and doesn't tell you if anything's been selected.

Private Sub btnsave_click()
Dim irow As Long
Dim ws As Worksheet
Dim arrValues()

    Set ws = Worksheets("13-14")
    
    With ws
        ' find first blank row in database
        irow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

        .Range("A" & irow) = TypePresEvent.Value
        .Range("B" & irow) = Topic.Value
        .Range("D" & irow) = Class.Value
        .Range("E" & irow) = Location.Value
        .Range("F" & irow) = Attendance.Value
        .Range("G" & irow) = Items.Value
        .Range("H" & irow) = Notes.Val

        For I = 0 To Presenters.ListCount - 1
            If Presenters.Selected(I) Then
                ReDim Preserve arrValues(X)
                arrValues(X) = Presenters.List(I)
                X = X + 1
                Presenters.Selected(I) = False
            End If

        Next I

        .Range("C" & irow).Value = Join(arrValues, ",")

    End With

    TypePresEvent.Value = ""
    Topic.Value = ""
    Class.Value = ""
    Location.Value = ""
    Attendance.Value = ""
    Notes.Value = ""

End Sub

 

[championem31]

THANK YOU SO MUCH, you are the greatest! Works like a charm!!