Add a single checkbox to select all checkboxes on a worksheet with one click.
If you have a large list of checkboxes, selecting all of the boxes, or removing the checks from all the boxes can be very time consuming.
This blog post will provide the code to select, or deselect, all checkboxes on a worksheet using a single checkbox, and explain how it works.
Watch the Video
In this example we have the check list shown below.
These checkboxes are created using the form control on the Developer tab.
Check out this tutorial for more information on how to create an interactive checklist.
Identify the Checkbox Name
Before we can start writing our VBA code, we need to be sure of the name of the checkbox we will be using to select, or deselect, all of the checkboxes.
You can do this by right mouse clicking on the checkbox, and then looking in the Name Box of Excel.
In this example, it is Check Box 2.
Excel VBA Code to Select all Checkboxes with a Single Checkbox
Open up the module in the Visual Basic Editor that you wish to store the code in, and copy and paste the code below.
Simply change the name of the checkbox to match what you have.
Sub AllCheckboxes()
Dim cb As CheckBox
For Each cb In ActiveSheet.CheckBoxes
If cb.Name <> ActiveSheet.CheckBoxes("Check Box 2").Name Then
cb.Value = ActiveSheet.CheckBoxes("Check Box 2").Value
End If
Next
End Sub
If this is not something you are familiar with, enrol in the complete Excel VBA course for beginners. You will be proficient in Excel VBA in just a few hours.
The VBA Code Explained
The macro begins by declaring a variable named cb to act as a checkbox.
This will make it simple to loop through all of the checkboxes on a worksheet. We will not have to worry about the checkbox names, and it also shortens the code used.
A For Each Next loop is used to loop through the collection of worksheet checkboxes.
An IF statement is used to check that the current checkbox is not Check Box 2, and if it is then the macro is not continued.
If the checkbox is not Check Box 2, then its value is made the same as Check Box 2. So if Check Box 2 is selected, they all get selected. And if it is deselected, they are all deselected.
Assign the Macro to the Checkbox
The macro now needs to be assigned to the checkbox, so that it will run when the box is clicked.To do this, simply right mouse click on the select all/deselect all checkbox, and choose Assign Macro from the shortcut menu.
You will be asked what macro to assign. Choose the AllCheckboxes macro (or whatever you may have named it) and click Ok.
Leave a Reply