How would I clear all ActiveX checkboxes upon opening a file?

[DreyFox]

Hello!

I'd like to clear the values and enable all disabled checkboxes of a workbook upon opening it. I read this article for guidance, but I'm not sure if this will work for ActiveX or not: How to clear specified cell contents on open and exit in Excel workbook? (extendoffice.com)

Any advice would be greatly appreciated. Thank you!

 

[GWteB]

This ClearAllCheckBoxes() procedure is likely to do what you want. Paste the code below in the ThisWorkbook module.

Private Sub Workbook_Open()
    Call ClearAllCheckBoxes
End Sub

Sub ClearAllCheckBoxes()

    Dim oWs         As Worksheet
    Dim oControl    As Object

    For Each oWs In ThisWorkbook.Worksheets
        For Each oControl In oWs.OLEObjects
            If TypeName(oControl.Object) = "CheckBox" Then
                oControl.Object.Enabled = True
                oControl.Object.Value = False
            End If
        Next oControl
    Next oWs
End Sub

 

[Norie]

That would only be of use if the checkboxes had a linked cell, and it wouldn't help with enabling them.

To uncheck and enable all the checkboxes in a workbook you could use something like this.

Sub ClearCheckboxes(wb As Workbook)
Dim ws As Worksheet
Dim ctl As OLEObject

    For Each ws In wb.Sheets
        For Each ctl In ws.OLEObjects
            If TypeName(ctl.Object) = "CheckBox" Then
                ctl.Object.Enabled = True
                ctl.Object.Value = False
            End If
        Next ctl
    Next ws
    
End Sub

 

[DreyFox]

That would only be of use if the checkboxes had a linked cell, and it wouldn't help with enabling them.

To uncheck and enable all the checkboxes in a workbook you could use something like this.

Sub ClearCheckboxes(wb As Workbook)
Dim ws As Worksheet
Dim ctl As OLEObject

    For Each ws In wb.Sheets
        For Each ctl In ws.OLEObjects
            If TypeName(ctl.Object) = "CheckBox" Then
                ctl.Object.Enabled = True
                ctl.Object.Value = False
            End If
        Next ctl
    Next ws
   
End Sub

Where do I copy and paste this?

 

[Norie]

The code I posted would go in a standard module and you could call it from the Open event of the workbook in the ThisWorkbook module like this.

Private Sub Workbook_Open()
    ClearCheckboxes Me ' Me refers to the workbook the code is in
End Sub

 

[GWteB]

Where do I copy and paste this?

Thought I was clear on that ......

 

[DreyFox]

The code I posted would go in a standard module and you could call it from the Open event of the workbook in the ThisWorkbook module like this.

Private Sub Workbook_Open()
    ClearCheckboxes Me ' Me refers to the workbook the code is in
End Sub

So if my work book was named say test, would I call ClearCheckboxes(test)?

 

[GWteB]

Have you seen my post #2?

 

[Norie]

So if my work book was named say test, would I call ClearCheckboxes(test)?

No, Me refers to the workbook the code is in regardless of it's name.

If you wanted to reset the checkboxes in a workbook name 'Test.xlsm' you would use this.

ClearCheckBoxes Workbooks("Test.xlsm")