Selecting All Checkboxes using a Single Checkbox in Excel

A few weeks ago, one of my readers left a comment on my blog asking if there is a way to select a bunch of checkboxes using a single checkbox. [Link to that comment]

This is not a new issue, when you have a huge form with many checkboxes, it becomes quite cumbersome to select each checkbox individually. And this gives rise to the need of a single checkbox that can check multiple checkboxes on a form.

So, in this post, I will show you how to do this in Excel.

Please note that I am assuming that you have basic knowledge of checkboxes in Excel. If you don’t have it, then I would strongly suggest you to go through this post.

See the below-animated image to know how our output would look like.

Note: Please note that you should have macros enabled on your system before performing this. If you are unsure about this, then have a look at this post.

Step by step instructions to do this in Excel:

Below is a step by step instruction to create a “Select All” checkbox in Excel. To make things easier we will refer to the “Select All” checkbox as ‘Master checkbox’ and all the other checkboxes as ‘Follower checkboxes’. So here we go:

• First of all add a checkbox to your sheet. You can add the checkbox by navigating to Developer Tab > Insert > Checkbox. Title this checkbox as “Select All”, this will be your Master Checkbox.

• Now, press Alt + F11, to open the VBA Editor, navigate to Insert > Module and paste the below code.

Sub SelectAll_Click()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
 If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name Then
 CB.Value = ActiveSheet.CheckBoxes("Check Box 1").Value
 End If
Next CB
End Sub

Note: Make sure that the name of your Master Checkbox is “Check Box 1”. If not, then change the “Check Box 1” in the above code with the name of your Master Checkbox.

You can check the name of your Master Checkbox in the “Name Box”.

This code makes sure that when your Master Checkbox is checked, all the other checkboxes (Follower checkboxes) on the active sheet should follow it i.e. they should have the same value as it has.

• After this, go back to the spreadsheet and right-click Master Checkbox, and from the drop-down select the option “Assign Macro”.

• Now, the “Assign Macro” window will open, click the “SelectAll_Click” macro and press “OK”. This will assign the “SelectAll_Click” macro on the click event of your Master Checkbox.

• Next, again go back to the VBA Editor and paste the below code there.

Sub Mixed_State()
Dim CB As CheckBox
For Each CB In ActiveSheet.CheckBoxes
 If CB.Name <> ActiveSheet.CheckBoxes("Check Box 1").Name And _
 CB.Value <> ActiveSheet.CheckBoxes("Check Box 1").Value And _
 ActiveSheet.CheckBoxes("Check Box 1").Value <> 2 Then
 ActiveSheet.CheckBoxes("Check Box 1").Value = 2
Exit For
 Else
 ActiveSheet.CheckBoxes("Check Box 1").Value = CB.Value
 End If
Next CB
End Sub

Note: Make sure the name of your Master Checkbox is “Check Box 1”. If not, then change the “Check Box 1” in the above code with the name of your Master Checkbox.

This code makes sure that your Master Checkbox, changes its state if you individually change the state of any of the Follower Checkboxes.

• Now, add the Follower Checkboxes one by one, and on the click event of each Follower, the checkbox assigns the “Mixed_State” macro. You can also assign the “cell_link” property for each Follower Checkbox, but this is not mandatory.
• After this simply test your checkboxes and you are done.

Get the spreadsheet containing this example here. And do let us know in case you have any doubts about this tutorial.