Tick All ActiveX CheckBoxes in Excel

All of the check boxes in an Excel worksheet can be cleared with the help of VBA.  Unticking all the checkboxes just requires a small amount of code.  If you need to clear all of the ActiveX checkboxes or select (tick) all of the checkboxes in your worksheet, in one go, the following Excel VBA procedure will provide an example.

The following is the code to tick all of the ActiveX box controls in a sheet.

Sub CheckAll()
Dim ws As Worksheet
Dim oj As OLEObject
Set ws=ActiveSheet
'Cycle through all of the check boxes in the ActiveSheet
For Each oj In ws.OLEObjects
If TypeName(oj.Object)="CheckBox" Then
oj.Object.Value=True
End If

Next oj

End Sub

CLEAR ALL CHECKBOXES ON A SHEET

The opposite is to clear all of the checkboxes and this will do that.

Sub Clear()
Dim ws As Worksheet
Dim oj As OLEObject
Set ws=ActiveSheet
Cycle through all of the check boxes in the ActiveSheet
For Each oj In ws.OLEObjects
If TypeName(oj.Object)="CheckBox" Then
oj.Object.Value= False
End If
Next oj

End Sub

Now that you have been through the above and understand it the following does the same thing.

Sub QuickClr()
If [b20]= True Then [b20:g20]=False Else [b20:g20]= True
End Sub

Or if you wanted to improve on the above an interested reader wrote in with this improvement.

Sub QuickClr1()
[b20:G20]= Not [b20]
End Sub

This is very tight!!!

It will either clear all check boxes or populate all check boxes based on the result of Cell B20 which has either True or False within it.  The attached files shows the three procedures working in a practical environment.