Select/Deselect all Checkboxes with a Single Checkbox

0 Flares Filament.io 0 Flares ×

Add a single checkbox to select, or deselect, all of the 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/deselect all the 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.

Select all boxes with a single checkbox

These checkboxes are created using the form control on the Developer tab.

Insert a checkbox form control

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.

Identify the name of the checkbox

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.

More Excel VBA Tutorials

Leave a Reply

Your email address will not be published. Required fields are marked *