VBA Select/Unselect Checkboxes

[Kappy]

I have a simple code to Select/Unselect all the checkboxes (Form Control) on the Active sheet.

The code works in that the values of the target cell change correctly from TRUE to FALSE and visa versa..

BUT, the checkboxes themselves don't show the change (i.e., the check marks in the boxes don't appear/disappear) until I run a second macro (literally any other macro) afterward.

My code is below, thank you for your help!



Sub Select_All()
ActiveSheet.CheckBoxes.Value = True
ActiveSheet.Calculate
End Sub

Sub UnSelect_All()
ActiveSheet.CheckBoxes.Value = False
ActiveSheet.Calculate
End Sub

 

[mikerickson]

Try

Sub CheckAllBoxes()
    SetAllFormsCheckboxes True
End Sub
Sub UnCheckAllBoxes()
    SetAllFormsCheckboxes False
End Sub
Sub toggleAllBoxes()
    SetAllFormsCheckboxes False, True
End Sub

Sub SetAllFormsCheckboxes(ByVal newValue As Boolean, Optional Toggle As Boolean)
    Dim oneShape As Object
    For Each oneShape In ActiveSheet.Shapes
        With oneShape
            If .Type = msoFormControl Then
                If .FormControlType = xlCheckBox Then
                    If Toggle Then newValue = Not (.ControlFormat.Value = xlOn)
                    .ControlFormat.Value = IIf(newValue, xlOn, xlOff)
                End If
            End If
        End With
    Next oneShape
End Sub

 

[Kappy]

Thanks!