Excel VBA Userform CheckBox check mark does not appear

0
votes

I have created an UserForm in Excel. The UserForm has a ListBox and a CheckBox added to it.

I have written VBA code to populate the ListBox with data in the 1st column of the UserForm_Data worksheet. I am attempting to add a Select All CheckBox to the UserForm. When I click on the CheckBox once, the check mark does not appear but the If Me.CheckBox.Value = True section of the Checkbox1_Change event is executed and all the items in the ListBox are selected. The check mark appears only when I click the CheckBox the second time. The Excel VBA code and an image of the UserForm are attached.

Option Explicit

Private Sub ListBox1_Change()

Dim i As Long

If CheckBox1.Value = True Then
    For i = 0 To Me.ListBox1.ListCount - 1
    
        If Me.ListBox1.Selected(i) = False Then
            Me.CheckBox1.Value = False
        End If
    Next i
End If

End Sub

Private Sub CheckBox1_Change()

    Dim i As Long
 
    If Me.CheckBox1.Value = True Then
        With Me.ListBox1
            For i = 0 To .ListCount - 1
                .Selected(i) = True
            Next i
        End With
    Else
        i = 0
    End If
 
End Sub

Private Sub UserForm_Initialize()

    Dim rng1 As Range
    Dim ws1 As Worksheet
    Dim i, lastRow As Long
    Dim list1 As Object
    Dim string1 As String
    Dim array1 As Variant
    
    Set list1 = CreateObject("System.Collections.ArrayList")
    Set ws1 = ThisWorkbook.Worksheets("UserForm_data")
    
    lastRow = ws1.UsedRange.Rows.Count
    
    Me.ListBox1.Clear
    
    For i = 2 To lastRow
        string1 = CStr(ws1.Cells(i, 1).Value)
        If Not list1.Contains(string1) Then
            list1.Add string1
        End If
    Next i
    
    array1 = list1.ToArray
       
    Me.Caption = "UserForm1"
    Me.ListBox1.list = array1
    Me.ListBox1.MultiSelect = 1
    Me.CheckBox1.Value = False

End Sub
2
vba
The 2 Change events are causing the problem. CheckBox1_Change is changing ListBox1, which fires the ListBox1_Change event, which is changing CheckBox1, which fires the CheckBox1_Change event. You get the idea. The end result is what you are experiencing. – Brian M Stafford
I added Application.EnableEvents = False and Application.EnableEvents = True to the start and end of the 3 subprocedures to prevent the events from firing each other. But I am still getting the same error. – busy bee
That's the right idea, but it only affects application events not control events. So one solution would be a global flag to implement your idea. – Brian M Stafford
I resolved the issue by modifying my ListBox1_Change event so it sets CheckBox1 to False if any item is not selected and exits the Sub. However, at the end of the Sub I set CheckBox1 to True. Thank you for your help. – busy bee

2 Answers

0
votes

There are two steps you can take to address this:

  1. There's a chance that simply adding a DoEvents at the end of the CheckBox1_Change event will force the redraw.

  2. If that doesn't work, add the following line just above the DoEvents and test it again... this encourages a screen update...

    Application.WindowState = Application.WindowState

0
votes

One approach is to use global flags to toggle on and off the control event handlers. Here is what the updated events would look like:

Option Explicit

Private Sub ListBox1_Change()
    Dim i As Long
    
    If Not AllowListBoxEvents Then Exit Sub
    
    AllowCheckBoxEvents = False
    
    If CheckBox1.Value = True Then
        For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = False Then CheckBox1.Value = False
        Next i
    End If

    AllowCheckBoxEvents = True
End Sub

Private Sub CheckBox1_Change()
    Dim i As Long
 
    If Not AllowCheckBoxEvents Then Exit Sub
 
    AllowListBoxEvents = False
 
    If CheckBox1.Value = True Then
        For i = 0 To ListBox1.ListCount - 1
            ListBox1.Selected(i) = True
        Next i
    End If

    AllowListBoxEvents = True
End Sub

Make sure you set the "Allow" variables to True in the Initialize event.