How to Quickly Select or Unselect all Checkboxes in Your Excel Worksheet by VBA

Checkbox is a good helper when you will analyze certain information. And in today’s article, we are going to introduce the method to quickly select or unselect all checkboxes in your Excel worksheet.

Checkbox is a kind of form control object in Excel. You will sometimes mark some options by checkboxes. The image below demonstrates a list about the products.

You can use the checkboxes to select or unselect certain products. While in some analysis, you will need to select all the checkboxes. Select them one by one is time consuming. Therefore, there is one checkbox in cell B1. You may follow the steps below and to realize this purpose.

Select or Unselect all Checkboxes

1. Click the tab “Developer” in the ribbon.
2. And then click the button “Visual Basic” in the toolbar.

Thus, you will activate the Visual Basic editor. On the other hand, you can also press the shortcut key “Alt +F11” on the keyboard.

3. In the editor, click the tab “Insert”.
4. After that, click the third option “Module” in submenu. And now, you have also inserted a new module in the editor.
5. Now, paste the following VBA codes into the new module.

Sub SelectUnselectAll()

    Dim CheBox As CheckBox

    ‘change the text of the first checkbox

    If ActiveSheet.CheckBoxes(“Check Box 1”).Value = 1 Then

        ActiveSheet.CheckBoxes(“Check Box 1”).Characters.Text = “Clear All”

    Else

        ActiveSheet.CheckBoxes(“Check Box 1”).Characters.Text = “Select All”

    End If

    For Each CheBox In ActiveSheet.CheckBoxes

        CheBox.Value = ActiveSheet.CheckBoxes(“Check Box 1”).Value

    Next CheBox

End Sub

In the above example, the first checkbox is “Check Box 1” in the target worksheet. In your worksheet, you may modify it according to the actual need.

6. After that, come back to the worksheet.
7. Now right click the first checkbox.
8. And then click the option “Assign Macro” in the sub menu.
9. In the “Assign Macro” window, click the name of the macro in the list.
10. And then click the “OK” button in the bottom. Thus, you have assigned the macro for this checkbox.
11. Now select the first checkbox in the worksheet. The text of the first checkbox will change and all the other checkboxes will also be selected.

And when you need to unselect all checkboxes, you can continue unselect the first checkbox. The value of other checkboxes will also change accordingly.

If you don’t need to select or unselect all checkboxes, you can clear the first checkbox. And next you can still select certain checkbox in the worksheet.

The VBA procedure for the first checkbox will not affect the single action of a single checkbox.

Excel Files Corruption and Other Data Issues

Excel files corruption has been a severe problem among all the users. If you happen to meet with Excel corruption, you will need to stop working and try to repair Excel. What’s worse, the Excel file corruption will affect other applications and files in your computer, especially the files and software of Office. Hence, the result of the Excel data disaster is unimaginable. To take remedial measures, you can use our software. This software has been used by many data disaster victims and has got a good feedback. Besides, it can repair Excel in a quick time and get back all your lost data and information.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair docx file error and outlook repair software products. For more information visit www.datanumen.com