Allow null input when retrieving data from multiselect listbox

Question

I'm trying to create a simple worksheet-based form that will pull data from the selections into another sheet on the Excel workbook. This is my first time messing with Visual Basic and ActiveX controls and I don't have much programming experience, but with a lot of Googling I've managed to muddle through some so far.

The part in question: I have a couple of multiselect boxes that, with the click of a button, push the data into the spreadsheet, using this code:

Private Sub CommandButton1_Click()
    Dim I As Long
        Range("A10").Select
        Range(Selection, Selection.End(xlToRight)).ClearContents
    With Me.ListBox1
        For I = 0 To .ListCount - 1
            If .Selected(I) Then
                Flg= True
                txt = txt & "," & .List(I)
            End If
        Next
    End With
    If Flg Then
        With Sheets("Sheet1")
            .Range("A10").Value = Mid$(txt, 2)
        End With
    End If
    txt=""
'Repeat for each listbox'
End Sub

As long as the user has selected at least one item in each listbox, this works fine to pull the data, and from there I can do what I need. But I don't want to require the user to click in each box (that is, I suppose I could force them to click a null selection if they don't want to select something in that box, but it would be easier to just have them not select anything at all). But (understandably) when nothing is selected in a given box, the code I pasted above returns run-time error 1004: No data was selected to parse.

How can I permit the user to make no selection in a box, and have the code just leave the associated cell blank when the data are retrieved?

MCard MCard · Accepted Answer · 2017-04-28T01:24:02

Wow, after a couple of days of looking I finally found it. This is the code that worked for me:

Private Sub ListBox1_LostFocus()
    Dim listItems As String, i As Long

    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) Then listItems = listItems & .List(i) & ", "
        Next i
    End With

    If Len(listItems) > 0 Then
        Range("A2") = Left(listItems, Len(listItems) - 2)
    Else
        Range("A2") = ""
    End If

End Sub

And it came from this page: https://www.mrexcel.com/forum/excel-questions/584437-write-selections-excel-listbox-cell.html ...thanks to 'Marcelo Branco' for providing that answer 7 years ago!

EDIT - This really answers a separate question that I had (about retrieving the data automatically), but it seems to also work for the big question here, since when nothing is selected, the target cell is blank.

Allow null input when retrieving data from multiselect listbox

1 Answers