If you have MS Excel 2010 or 2013 and you are using the Developer tab to insert form controls or ActiveX controls like list boxes, or combo boxes, you might need to delete duplicate or unused controls.
By default, when you want to delete a form control like a combo box and you delete the rows where the combo boxes are placed, only the rows are deleted but the combo boxes will float and pile up or overlap with other controls.
It is always useful if you find a way to “Select All” form controls or ActiveX controls in your Excel worksheet so that you can perform basic formatting actions on them at once. In this article, we will work with combo boxes.
The Problem
In an Excel file, I added combo boxes with pull down values. When I tried to delete combo boxes that are redundant or unwanted, I just selected the row where the combo boxes were placed and then deleted the row thinking that the combo boxes will be deleted as well. Wrong. Yes, the rows selected were deleted but the combo boxes are floating objects so they were not deleted. As a result, the combo boxes moved down overlapping the other combo boxes.
The real problem begins when a huge number of combo boxes are piled up overlapping each other because when you delete the combo box, though the top combo box is deleted, you will feel that it is not deleted because there is still a combo box left. If you do not realize that there are more than twenty (20) combo boxes piled up there, you can easily conclude that your Excel program has a problem because it is not deleting a combo box!
The Solution
First, test if the combo boxes are indeed duplicates overlapping or piling up on top of each other. This can be done by moving the top combo box away and see if indeed, there are still combo boxes left in place. To do the moving, drag the combo box with the right mouse button. When you release the right mouse button, a context menu will appear and click on “Move Here”.
After moving, if there are still combo boxes left in the original place, it means that several combo boxes are piled up on top of each other.
After moving several combo boxes, there are still combo boxes in the original location. If there are 1000 combo boxes in all, you need to move the combo box 1000 times so that you can delete each one after all. While the controls are still overlapping, you cannot exactly know as to how many combo boxes are there in all.
The good news is, you can easily Select All combo boxes and then DELETE all of them at once!
Step 1: Right click on the combo box to select it. But a context menu will appear. Do not be tempted to click on CUT in the context menu since it will only delete the combo box that you right clicked.
Step 2: Instead, press the ESC key to close the context menu while the combo box is still selected.
Step 3: Now, press CTRL + A which means “Select All”. That is, press CTRL and hold, then press the letter A. In this case, it will select all combo boxes in the worksheet. If the selected control is a list box, it will select all list boxes. This wqrks with all types of controls.
Step 4: Now, press the DEL key in your keyboard to delete all selected combo boxes!
NOTE:
- CTRL + A will select all controls in the worksheet. If there are legitimate controls that you want to deselect or unselect so that they are NOT deleted, press CTRL key and hold it, then click on a control that you want to deselect or unselect.
- This series of steps were done in Excel 2010 but will still be applicable in the newer versions of Excel.