Loop Through Listboxes to Determine Which have no Selections and Send User Message

[NorthbyNorthwest]

Hi, everyone. I have a group of listboxes that I check to determine if nothing selected. If nothing selected, users get a message box advising them to select at least one item. So, users get multiple message boxes if there are multiple listboxes with no selection. Is there a way to loop through this specific group of listboxes (there are other listboxes in form) and send one message advising users of all listboxes needing a selection. Example: MsgBox "Please make selections for listboxes 2 and 5." In code below iCount = 0 indicates listboxes with no selection. So, I'm guessing you have to loop through iCounts that equal zero?

If OptButton2.value = True Then
    For N = 0 To lbxList1.ListCount - 1
        If lbxList1.Selected(N) = True Then
            iCount = iCount + 1
        End If
    Next N
    If iCount = 0 Then MsgBox "Please select at least one item from listbox 1"
End If

If optButton5.value = True Then
    For N = 0 To lbxList2.ListCount - 1
        If lbxList2.Selected(N) = True Then
            iCount = iCount + 1
        End If
    Next N
    If iCount = 0 Then MsgBox "Please select at least one item from listbox 2"
End If

If optButton8.value = True Then
    For N = 0 To lbxList3.ListCount - 1
        If lbxList3.Selected(N) = True Then
            iCount = iCount + 1
        End If
    Next N
    If iCount = 0 Then MsgBox "Please select at least one item from listbox 3"
End If

If optButton11.value = True Then
    For N = 0 To lbxList4.ListCount - 1
        If lbxList4.Selected(N) = True Then
            iCount = iCount + 1
        End If
    Next N
    If iCount = 0 Then MsgBox "Please select at least one item from listbox 4"
End If

If optButton14.value = True Then
    For N = 0 To lbxList5.ListCount - 1
        If lbxList5.Selected(N) = True Then
            iCount = iCount + 1
        End If
    Next N
    If iCount = 0 Then MsgBox "Please select at least one item from listbox 5"
End If

If optButton17.value = True Then
    For N = 0 To lbxList6.ListCount - 1
        If lbxList6.Selected(N) = True Then
            iCount = iCount + 1
        End If
    Next N
    If iCount = 0 Then MsgBox "Please select at least one item from listbox 6"
End If

 

[Micron]

Very possible.
declare a string variable (Dim strMsg As String), change this: If iCount = 0 Then MsgBox "Please select at least one item from listbox 1"
to this
If iCount = 0 Then str = strMsg & "- listbox 1" & vbcrlf Note - each section adds the needed name, so next section is
If iCount = 0 Then str = strMsg & "- listbox 2" & vbcrlf
and so on.
After the last If block put

If Not strMsg = "" Then
   strMsg = "The following listboxes require at least one item to be selected:"& vbcrlf & strMsg
End If

See if that's close to what you want.
EDIT - I think there's a lot more you can do to shorten that code if interested.

 

[Micron]

If iCount = 0 Then str = strMsg

A boo boo there. Should be strMsg = strMsg

 

[Micron]

I have to go out for the evening so I'll post a shortened but untested version. There may be an opportunity for further condensing if those option buttons can be looped, but it just might result in about the same number of code lines.

Dim strMsg As String

If OptButton2 Then
   If lbxList1.ItemsSelected.Count = 0 Then strMsg = strMsg & "- listbox 1" & vbCrLf
End If

If optButton5 Then
   If lbxList2.ItemsSelected.Count = 0 Then strMsg = strMsg & "- listbox 2" & vbCrLf
End If

If optButton8 Then
   If lbxList3.ItemsSelected.Count = 0 Then strMsg = strMsg & "- listbox 3" & vbCrLf
End If

If optButton11 Then
   If lbxList4.ItemsSelected.Count = 0 Then strMsg = strMsg & "- listbox 4" & vbCrLf
End If

If optButton14 Then
   If lbxList5.ItemsSelected.Count = 0 Then strMsg = strMsg & "- listbox 5" & vbCrLf
End If

If optButton17 Then
   If lbxList6.ItemsSelected.Count = 0 Then strMsg = strMsg & "- listbox 6" & vbCrLf
End If

If Not strMsg = "" Then
   strMsg = "The following listboxes require at least one item to be selected:" & vbcrlf & strMsg
End If

 

[NorthbyNorthwest]

Thanks so much for assisting. I got a compile error "Method or data member not found."

 

[NorthbyNorthwest]

I left out the compile error was on "ItemsSelected.

 

[Micron]

Please post your code so I can see it & use code tags as I did. btw, I thought it was an hour later than it is, so there might be time if we hurry.

 

[Micron]

...and where is the code? Userform or sheet? Or standard module?
I used Access so I'd have to test in Excel too I suppose. You'd think they'd be the same, but I've ranted about that already.

 

[NorthbyNorthwest]

Userform.

 

[Micron]

Unbelievable. Excel listbox does not seem to provide that property yet Access does. Who else would design different applications using what should be the same controls in Office this way??
At least I was able to show how to construct one message. Sorry about the other. Just use what I wrote for the message stuff and you should be OK.