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