Uncheck all checkboxes across entire workbook via CommandButton

Question

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

score 5 · Accepted Answer · 2013-08-22T11:56:42.090

5

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

edited Aug 22 '13 at 11:56

answered May 29 '13 at 16:05

Thank you mehow for your help. Something quite odd is happening: This appears to work for 2 sheets for which I have created the boxes manually; however, it doesn't appear to work for subsequent sheets, for which I have simply copy pasted the checkboxes and VBA code. I have checked multiple times, and the name of these items are identical to those that I created manually. I really don't see why this wouldn't work? Creating all the boxes manually is not feasible as I have way too many sheets for that. Thanks a lot for your help. – jcv May 29 '13 at 16:28
@jcv i see. I bet its something really simple you have somehow overlooked man. open a new workbook and try creating some checkboxes manually and copy some over to other sheets. then run this code and see the behavior. i have tested on my machine and everything works fine. In your case put some `Debug.Print` lines, to see if you are really iterating through all worksheets, all checkboxes etc – May 29 '13 at 16:33
Mehow, thanks man. I have done some analysis and came across a new error - please see my updated main post. When I run your VBA code, I get an error in the CheckBox code @ Case False [...] ActiveSheet.Shapes etc. Can you have a look please? – jcv May 29 '13 at 16:48

score 2 · Answer 2 · edited May 29 '13 at 16:18

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.

edited May 29 '13 at 16:18

Stewbob

16,759
9
63
107

answered May 29 '13 at 15:42

Bathsheba

231,907
34
361
483

Bathsheba, thank you for your quick reply. I tried both of your suggestions and they yield a code error, stating "Expected: expression" and "Object doesn't support this property or method" respectively. They are ActiveX Control elements. – jcv May 29 '13 at 15:46
Which version of Excel are you using? And, are the check boxes form controls or ActiveX controls (or don't you know)? – Bathsheba May 29 '13 at 15:48
Excel 2007 and ActiveX Controls as stated above – jcv May 29 '13 at 15:49
Thanks, really appreciate your help! However, there still appears to be a problem... I have updated my main post. – jcv May 29 '13 at 16:52

Uncheck all checkboxes across entire workbook via CommandButton

2 Answers2

Linked

Related