Selecting multiple items from a listbox

I would like to be able to select multiple items from a listbox and then act on that selection list. Each item selected would initiate separate processing later in my code. I don't do alot with forms and am having trouble finding a good example of that.

1. Select multiple items form the list box.
2. If none is selected then assume all items in the box are to be used
3. Read each item to a separate variable and initiate code later on in the script based on the variable.

I imagine that I could read the listbox multiple times for to init the subsequent steps which just consists mainly of importing various data files.
Who is Participating?
Helen FeddemaConnect With a Mentor Commented:
Make it a multi-select listbox and use code like the following:

Private Sub cmdProcessListboxItems_Click()

On Error GoTo ErrorHandler

   Dim lst As Access.ListBox
   Dim strData As String
   Dim strPrompt As String
   Dim strTest As String
   Dim strTitle As String
   Dim varItem As Variant
   Set lst = Me![lstSelectContacts]
   'Check that at least one item has been selected
   If lst.ItemsSelected.Count = 0 Then
      strTitle = "No items selected"
      strPrompt = "Please select at least one item"
      MsgBox prompt:=strPrompt, _
         buttons:=vbInformation + vbOKOnly, _
      GoTo ErrorHandlerExit
   End If
   strTitle = "Information missing"
   'Test for required information, using listbox columns
   For Each varItem In lst.ItemsSelected
      'Check for required address information (or whatever you need to check)
      strTest = Nz(lst.Column(5, varItem))
      Debug.Print "Street address: " & strTest
      If strTest = "" Then
         strPrompt = "Skipping this record -- no street address!"
         MsgBox prompt:=strPrompt, _
            buttons:=vbExclamation + vbOKOnly, _
         GoTo NextItem
      End If
      'Do something with info from the listbox columns, using
      'this syntax
      strData = Nz(lst.Column(5, varItem))
   Next varItem

   Exit Sub

   MsgBox "Error No: " & Err.Number _
      & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

Helen FeddemaCommented:
I think it is unwise to assume that if nothing is selected, everything should be processed.  For listboxes with many items, I usually add two buttons, one for selecting everything and one for deselecting everything.  This is safer.  Here is the code for selecting all -- for deselecting, change True to False:

Set lst = Me![lstSelectMultiple]
intRows = lst.ListCount - 1

For intIndex = 0 To intRows
   lst.Selected(intIndex) = True
Next intIndex

Open in new window

All Courses

From novice to tech pro — start learning today.