Uncheck all checkboxes across entire workbook via CommandButton

I would like to have a code that unchecks all checkboxes named "CheckBox1" for all sheets across the workbook. My current code unfortunately doesn't work, and I'm not sure why - it only works for the active sheet.

Private Sub CommandButton1_Click()

    Dim Sheet As Worksheet
    For Each Sheet In ThisWorkbook.Worksheets

        Select Case CheckBox1.Value
        Case True: CheckBox1.Value = False
        End Select  
    Next
End Sub

Solution 1:

This code iterates through all sheets (except sheets named Sheet100 and OtherSheet) and unchecks all your ActiveX checkboxes named CheckBox1

Sub uncheck_boxes()

    Dim ws As Worksheet
    Dim xbox As OLEObject
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Sheet100" And ws.Name <> "OtherSheet" Then
            For Each xbox In ws.OLEObjects
                ws.OLEObjects("CheckBox1").Object.Value = False
            Next
        End If
    Next
End Sub

To uncheck all ActiveX checkboxes in all sheets disregarding the names used

Sub uncheck_all_ActiveX_checkboxes()

    Dim ws As Worksheet
    Dim xbox As OLEObject
    For Each ws In ThisWorkbook.Worksheets
        For Each xbox In ws.OLEObjects
            ws.OLEObjects(xbox.Name).Object.Value = False
        Next
    Next
End Sub

To uncheck all Form Control checkboxes on a spreadsheet use

Sub uncheck_forms_checkboxes()

    Dim ws As Worksheet
    Dim xshape As Shape
    For Each ws In ThisWorkbook.Worksheets
        For Each xshape In ws.Shapes
            If xshape.Type = msoFormControl Then
                xshape.ControlFormat.Value = False
            End If
        Next
    Next
End Sub

Solution 2:

[edited following comments]

Try this:

Sub test()
Dim ws As Excel.Worksheet
Dim s As Object

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Definitions" And ws.Name <> "fx" Then
        Set s = Nothing
        On Error Resume Next
        Set s = ws.OLEObjects("CheckBox1")
        On Error GoTo 0
        If Not s Is Nothing Then
            s.Object.Value = False
        End If
    End If
Next ws

End Sub

This is a global function (it doesn't belong to a particular sheet), but you can put it inside CommandButton1_Click() if you want.

You might not need the error blocking if your sheets (other than Definitions and fx) always contain CheckBox1. Alternatively remove that if statement.