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]

I think I would use a function to return the number instead of writing the same counting code many times.

 

[NorthbyNorthwest]

I'll try. Thanks again. I'll let you know how it went. Hope you have a great evening out.

 

[Micron]

The function might look like

Function GetCount(lstbx As Listbox) As Integer
Dim N As Integer, iCount As Integer

For N = 0 To lstbx.ListCount - 1
    If lstbx.Selected(N) = True Then iCount = iCount + 1
Next N
GetCount = iCount

End Function

For one test, maybe this

Dim strMsg As String
Dim intCount As Integer

If OptButton2 Then
   intcount = GetCount(lbxlist1)
   If intCount = 0 Then strMsg = strMsg & "- listbox 1" & vbCrLf
End If

Untested, of course.

Hope you have a great evening out.

Thanks!

 

[Micron]

Maybe this too, and lose a variable
If GetCount(lbxlist1) = 0 Then strMsg = strMsg & "- listbox 1" & vbCrLf

 

[Micron]

I monkey around with this stuff because I want to learn. Not sure why because I'll probably never use it outside of these forums. Anyway, I discovered that the correct syntax for a function is a bit different than what I expected. In testing, my function works but the rest of it is a simple message box and not stringing together a message based on a whole bunch of option buttons. Maybe you are happy with what I suggested long ago, or there is the opportunity to condense like below. Notice that it is possible to eliminate iCount as well. I hope you have the opportunity to try the condensed version. If not, perhaps you will get the idea re: creating one message string to build a message containing multiple parts.

'sub line is missing
Dim strMsg As String

If OptButton2 Then
   If GetCount(lbxlist1) = 0 Then strMsg = strMsg & "- listbox 1" & vbCrLf
End If

If optButton5 Then
   If GetCount(lbxlist2) = 0 Then strMsg = strMsg & "- listbox 2" & vbCrLf
End If

If optButton8 Then
   If GetCount(lbxlist3) = 0 Then strMsg = strMsg & "- listbox 3" & vbCrLf
End If

If optButton11 Then
   If GetCount(lbxlist4) = 0 Then strMsg = strMsg & "- listbox 4" & vbCrLf
End If

If optButton14 Then
   If GetCount(lbxlist5) = 0 Then strMsg = strMsg & "- listbox 5" & vbCrLf
End If

If optButton17 Then
   If GetCount(lbxlist6) = 0 Then strMsg = strMsg & "- listbox 6" & vbCrLf
End If

'End sub line is missing

Function GetCount(lstbx As msforms.Listbox) As Integer '<< this change is vital
Dim N As Integer

For N = 0 To lstbx.ListCount - 1
    If lstbx.Selected(N) = True Then GetCount = GetCount + 1
Next N

End Function

 

[NorthbyNorthwest]

Hi, Micron. The last suggestion using a function worked beautifully. Genius! Besides being more concise, it solved a problem I had with the code I submitted and with your first suggestion. The problem involved multiple listboxes with no selection. Example: If users clicked button to copy record to workbook, they received the message that multiple listboxes needed selections. However, if two listboxes needed selections and the user addressed one and not the other, when the sub routine reran it ignored the fact that one listbox was still in need of a selection. It accepted the record. I don't understand why the code would behave this way.

But then I tried your last code using the function. And, when the sub routine ran a second time it recognized that one listbox with no selection remained and presented the user with a second message. Thanks so much for helping me. Both you and this forum are awesome.

 

[Micron]

Thanks for the kind words and recognition. If you step through your old code (F8) maybe you could figure that out but not obvious to me. Possible to condense even more if all the If's were one-liners, but maybe let well enough alone!