EXCEL VBA - Data Entry with Multi-Selection UserForm List Box

Question

I'm trying to create a User Form that allows someone to pick some options and enter data into my excel sheet. In the user form, I have a List Box with several answers. I have it so that that the User can select multiple answers in the List Box.

If the User selects 2 answers, I want the excel sheet to register 2 rows of data. If the user selects 3 answers, I want the excel sheet to register 3 rows of data.

Basically I am doing exactly what is described here: http://www.excel-easy.com/vba/userform.html except in the "City Preference" ListBox, I can select multiple choices. I want the excel sheet to create a line item for each City Preference chosen, while holding all the other selections the same.

I'm thinking the code will be something like this:

For i = 1 to "total # of items selected in listbox"
     emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
     Worksheet.Cell(emptyrow,3).Value = "Selected item(i) from list box"
Next I

Thanks!

David Zemens David Zemens · Accepted Answer · 2014-09-10T16:45:51

Use a function like this to return an array of selected items:

Public Function GetSelectedItems(lBox As MSForms.ListBox) As Variant
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer

        selCount = -1
        For i = 0 To lBox.ListCount - 1
            If lBox.selected(i) = True Then
                selCount = selCount + 1
                ReDim Preserve tmpArray(selCount)
                tmpArray(selCount) = lBox.List(i)

            End If
        Next
        If selCount = -1 Then
            GetSelectedItems = Array()
        Else:
            GetSelectedItems = tmpArray
        End If
End Sub

Then modify your code like:

Dim selectedItems as Variant
selectedItems = GetSelectedItems(myListBox) 'Modify this line to refer to your listbox name

For i = lBound(selectedItems) to UBound(selectedItems)
     emptyrow = WorksheetFunction.CountA(Range("A:A")) + 1
     Worksheet.Cell(emptyrow,3).Value = selectedItems(i)
Next

EXCEL VBA - Data Entry with Multi-Selection UserForm List Box

1 Answers