How do I return multi select listbox values into a sentence using Word VBA?

Question

I've created a multi select listbox on a userform. There are 9 items in the listbox. How do I gather those selected items into a sentence?

The listbox contains reasons for returning a check. The items in the listbox are idenfiers or placeholders for a longer string, so the selection "unsigned", creates the returned string, "the check was not signed".

The user can select several reasons, so depending on the selections, I need sentence structure that formats as: "x, y, and z" OR "y and z" OR "z". (ex: "the check is not signed, the check is post-dated, and the check is a third-party check.")

It seems as though an array needs to be created from the selections, the selections counted, then an "If then" statement to create the sentence, but I'm stumped. I can count the selected items, I can create the sentence if only 1 item is chosen, but the compound sentence stumps me.

Answer 1

I have this function which returns an array of selected items from a listbox. I have updated from my original answer to return a delimited string instead of an array of selected items:

Public Function GetSelectedItems(lBox As MSForms.ListBox) As String
'returns an array of selected items in a ListBox
Dim tmpArray() As Variant
Dim i As Integer
Dim selCount As Integer
    selCount = -1
    '## Iterate over each item in the ListBox control:
    For i = 0 To lBox.ListCount - 1
        '## Check to see if this item is selected:
        If lBox.Selected(i) = True Then
            '## If this item is selected, then add it to the array
            selCount = selCount + 1
            ReDim Preserve tmpArray(selCount)
            tmpArray(selCount) = lBox.List(i)
        End If
    Next

    If selCount = -1 Then
        '## If no items were selected, return an empty string
        GetSelectedItems = "" ' or "No items selected", etc.
    Else:
        '## Otherwise, return the array of items as a string,
        '   delimited by commas
        GetSelectedItems = Join(tmpArray, ", ")
    End If
End Function

You can call this by assigning to an array:

Dim mySentence as String
mySentence = GetSelectedItems(MyUserForm.MyListBox)

From that point, you could just replace the last comma with a " and" and you should be all set.

Answer 2

This is very basic and I just quickly threw it together, but may work for what you're trying to do:

Private Sub ListBox1_Change()

    If ListBox1.Selected(0) = True Then
        msg1 = ListBox1.List(0)
    End If

    If ListBox1.Selected(1) = True Then
        msg2 = ListBox1.List(1)
    End If

    If ListBox1.Selected(2) = True Then
        msg3 = ListBox1.List(2)
    End If

    MsgBox msg1 & ", " & msg2 & ", " & msg3

End Sub

Answer 3

Forget the REDim array concept- can get confusing. A simple way to collect multiselect choices is the following

Sub SelectMulti()
picklist1 = ""
For i = 0 To ListBox1.ListCount - 1
   If ListBox1.Selected(i) = True Then
      Debug.Print i    ' optional 
      If picklist1 = "" Then
         picklist1 = ListBox1.List(i)
       Else
         picklist2 = ListBox1.List(i)
         picklist1 = picklist1 & ";" & picklist2
       End If
    End If
Next
Debug.Print picklist1

End sub