Checkboxes are great for making a sheet interactive. They make it easy for anyone to change a value and select or unselect an item.
However, if you no longer need them, you may find that they are not that easy to remove from the sheet (which probably explains why you’re reading this now). But don’t worry! I got you.
Deleting them may not be as straightforward, but once you have gone through the methods described below, I’m sure you will see that it’s not that difficult to do.
Two Types of Checkboxes
Before we can delete a checkbox, we must first determine the type of checkbox added to our sheet.
The type of checkboxes we have will determine the method we need to use to remove them.
There are 2 types of checkboxes:
1. Form Control Checkbox – this is built-in in Excel and is recommended for those who want a checkbox for simple purposes. If you don’t have any background on VBA but want to add checkboxes to your sheet, you should use this one because it doesn’t require any code. You can link it to a cell with just a few clicks.
2. ActiveX Control Checkbox – this is not built-in in Excel, so it runs separately when you open the file. This type of checkbox is perfect for advanced users who want to customize the appearance and other properties of the checkbox. This checkbox may require a strong knowledge of VBA for it to work well.
If you don’t know what type of checkbox is in your sheet, go to the Developer tab.
If you can’t find the Developer tab:
- Go to File >> More >> Options.
- Once the Excel Options menu appears, click on Customize Ribbon.
- On the right side of the menu (where there are green checkboxes), look for Developer, tick the checkbox and click OK.
From the Controls section, check if the Design Mode is selected. If it’s not, click on it.
We need to turn on the Design Mode to be able to select the checkboxes.
Right-click on your checkbox.
A checkbox is a Form Control if you see Assign Macro in the right-click menu.
On the contrary, a checkbox is an ActiveX Control if you see Properties.
How to Delete a Form Control Checkbox?
To delete a Form Control checkbox:
- While pressing the CTRL key, click on the checkbox you want to delete.
- Once the checkbox is selected, press DEL.
If you have turned on the Design Mode, remember to turn it off after deleting the checkboxes.
This step is optional for Form Controls, but it’s a good practice just in case you have ActiveX Controls added to your sheet.
How to Delete an ActiveX Control Checkbox?
To delete an ActiveX Control checkbox:
- Simply click on the checkbox you want to delete and press DEL.
- If you can’t select the checkbox, it means that the Design Mode is switched off. From the Developer tab, click on Design Mode to activate it.
- After deleting the checkboxes, don’t forget to switch off the Design Mode to have the remaining ActiveX controls in the sheet work again.
How to Delete Multiple Checkboxes in One Go (Regardless of Type)?
If you have multiple checkboxes that you want to delete and you’d like to remove them all at once (without clicking on them one by one), then this method is perfect for you.
1. Switch on the Design Mode.
To do this, go to the Developer tab and make sure that the Design Mode button is activated.
2. From the Home tab, go to the Editing section.
3. Click on Find & Replace >> Go to Special.
4. The Go To Special menu will appear. Select Objects and click OK.
5. You will be redirected back to your sheet. Notice that all the checkboxes and all other objects inside your sheet are now selected.
6. Go over each object selected in your sheet and make sure that only the checkboxes you want to be deleted are selected.
Unselect all other checkboxes and objects (e.g., buttons, shapes, tornado charts) that you don’t want to be removed.
You can do so by clicking on them while pressing the CTRL key.
In my example above, I only wanted to delete Checkbox2 and Checkbox3 from the Form and ActiveX Controls, so I made sure that they are the only ones selected.
7. Once the desired checkboxes are selected, press DEL.
And that’s it! The previously selected checkboxes should now be removed from your sheet.
After removing all the targeted checkboxes, remember to switch off Design Mode from the Developer tab. This step is necessary to have the remaining ActiveX Controls working again.
How to Delete All Checkboxes in One Go Using VBA?
This method is perfect for you if you have a lot of checkboxes in your sheet and would like to delete them all in one go using VBA.
1. Activate the sheet where you would like to delete the checkboxes.
This step is crucial because the code we will use will only delete the checkboxes from the active sheet.
2. Press ALT + F11. The VBA Editor will appear.
Click the Insert menu and select Module.
3. A new module will be inserted (shown on the left pane).
4. Paste the following code on that module.
Option Explicit
Sub DeleteAllCheckboxes_FormControl()
'use this to delete all Form Control checkboxes in the active sheet
Dim shp As Shape
For Each shp In ThisWorkbook.ActiveSheet.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType = xlCheckBox Then shp.Delete
End If
Next
End Sub
Sub DeleteAllCheckboxes_ActiveXControl()
'use this to delete all ActiveX Control checkboxes in the active sheet.
Dim shp As Shape
For Each shp In ThisWorkbook.ActiveSheet.Shapes
If shp.Type = msoOLEControlObject Then
If shp.OLEFormat.Object.OLEType = xlOLEControl Then shp.Delete
End If
Next
End Sub
5. Notice that there are two subroutines in the code. One is for deleting all Form Control checkboxes, while the other is for deleting all ActiveX Control checkboxes.
Run the subroutine that corresponds to the checkboxes you want to delete.
6. To run the code, click anywhere within the appropriate subroutine and press F5.
In my example above, I have placed the cursor in the second subroutine because I wanted to delete all the ActiveX Control checkboxes in my sheet.
7. After running the code, go back to your sheet and check if all the targetted checkboxes are removed.
Conclusion
Deleting checkboxes may not be as straightforward as expected, but I hope this article helps you see that it’s not actually that difficult to do. You only have to know first the type of checkboxes you want to delete. Once you’ve identified that, you can apply one of the methods described above.
And while you’re at it, learn how to delete other stuff in Excel, too with the following articles.