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.

Select All Checkboxes using a single Checkbox

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 the things easier we will refer 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.

Add Checkbox to Excel

  • 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

Add Macro on a new Module

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”.

Find the Name of a Checkbox

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.

Assign Macro to Checkbox

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

Select the "Select All" 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.

Add the Mixed State Macro in VBA

  • 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.

Select the Mixed State Macro

  • Now, add the Follower Checkboxes one by one, and on the click event of each Follower checkbox assign 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.

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

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Comments

  1. G.ANJANEYULU says:

    Hai
    Ankit kaul I really appreciate u buddy.
    I saw ur web link, but i need more about excel including basic also.
    I hope u help me out.
    Thanks
    G.NAJANEYULU.
    9642648494.

Privacy Policy | Disclaimer | About