VBA “All” checkbox with listbox deselect loop issues

1
votes

I have a check box that is used for "ALL" (selected by default) selections in a muliselect listbox that is deselected when an item in the listbox is selected. I also coded it so when "ALL" is selected, then it clears the listbox selections and checks the box. It ends up looping through the different subs and makes it annoying for the user.

For instance, when I click an item in the listbox it selects that value, then deselects the checkbox. Since the checkbox is deselected, it goes back through the listbox and deselects the selected item. It loops between the two subs a couple times and ends up only working correctly half the time.

Can I prevent entering the other sub? Is there better logic so it won't loop as it does? or maybe an better method to achieve this?

Multiselect listbox:

Private Sub Mkts_Change()

If Me.cheMkts.Value = True Then
Me.cheMkts.Value = False
End If

End Sub

Checkbox:

Private Sub cheMkts_Click()
Dim i As Integer

For i = 0 To Mkts.ListCount - 1
If Me.Mkts.Selected(i) = True Then
    Me.Mkts.Selected(i) = False
End If
Next
End Sub
2
vbacheckboxlistboxmulti-select

2 Answers

0
votes

What about adding an If statement around your cheMtks_Click()? This way when your code deselects it automatically it shouldn't trigger the loop.

Private Sub cheMkts_Click()
    If Me.cheMkts.Value = True Then
        Dim i As Integer

        For i = 0 To Mkts.ListCount - 1
            If Me.Mkts.Selected(i) = True Then
                Me.Mkts.Selected(i) = False
            End If
        Next
    End If
End Sub
0
votes

Thanks for your help, Ruben. That corrects the error on the one end, but I am still having issues on the other side. When I have a selection and click the "ALL" box it deselects the check.

I came up with this code, which works beautifully in combo to your suggestion, but only when I have one item selected. If there is anything more, then it still goofs up. Figured I would post to see if you or someone else could advise a solution for multiple selections.

Private Sub Mkts_Change()
Dim i As Integer, count As Integer

For i = 0 To Mkts.ListCount - 1
If Me.Mkts.Selected(i) = False Then
    count = count + 1
End If
Next


If Me.cheMkts.Value = True And count <> Mkts.ListCount Then
Me.cheMkts.Value = False
End If

End Sub