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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
This ClearAllCheckBoxes() procedure is likely to do what you want. Paste the code below in the ThisWorkbook module.

VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,267
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Solution

DreyFox

New Member
Joined
Nov 25, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,267
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
Private Sub Workbook_Open()
    ClearCheckboxes Me ' Me refers to the workbook the code is in
End Sub
 

DreyFox

New Member
Joined
Nov 25, 2020
Messages
44
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
Have you seen my post #2?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,267
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
ClearCheckBoxes Workbooks("Test.xlsm")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top