Excel’s Form Controls feature adds extra functionality and allows the user to interact with the worksheet. However, you might need to remove them from your worksheet. So in this article, we’ll show 3 handy ways how to remove a form control in Excel. In addition, we’ll also demonstrate how you can delete a macro button.
How to Remove a Form Control in Excel: 3 Quick Ways
First of all, let us consider the List of Employee Names dataset shown in the B4:C14 cells. Here, the dataset displays the ID and Employee Names respectively. Moreover, we can use the Scroll Bar to move across the list of employees. Now, you can remove Form Controls in many ways using Excel’s built-in options and VBA code. So, without further delay, let us see each method individually and in detail.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.
Method-1: Using Find and Select Option
Let’s begin with the simplest and most obvious method of removing Form Controls, that is to say, we’ll use Excel’s Find and Select option which selects the objects which can then be deleted.
📌 Steps:
- In the first place, click the Find & Select option, in the Editing group >> then click the Go To Special option.
Now, this opens the Go To Special wizard.
- Next, choose Objects >> hit the OK button.
- In turn, this selects all the objects in the worksheet >> press the DELETE key on your keyboard.
Finally, this eliminates the Scroll Bar as shown in the image below.
Read More: How to Create a Macro Button in Excel
Method-2: Using Keyboard Shortcut
Wouldn’t it be great if only there was a keyboard shortcut to remove Form Controls? Well, you’re in luck because our next method answers this exact question. Now, allow me to demonstrate the process in the steps below.
📌 Steps:
- To begin with, press the F5 key which opens the Go To dialog box.
- Now, click the Special option at the bottom.
- Then, choose Objects >> click the OK button.
- Following this, press the DELETE key to remove the Form Control object.
Eventually, your output should look like the picture given below.
Read More: How to Create Button Without Macro in Excel
Similar Readings
- How to Set Option Button Value in Excel VBA
- Insert Excel VBA Radio Button Input Box
- How to Add Up and Down Buttons in Excel
- Create Button to Link to Another Sheet in Excel
- How to Change Color of Toggle Button When Pressed in Excel
- How to Add Command Button Programmatically with Excel VBA
- How to Change Cell Value Using Toggle Button in Excel
Method-3: Applying VBA Code
You may wonder, is there a way to automate the same boring and repetitive steps? Then, I have some great news for you because you can apply VBA code to remove Form Controls from a spreadsheet so, just follow along.
Assuming the List of Idioms dataset shown in the B4:C14 cells where we have a handful of idioms. Here, we can press the Change to Uppercase button to run a macro that will change the first letter of the idioms to uppercase.
3.1 Removing Form Controls Buttons
Now, the following steps will show us how to get rid of the button using the VBA code.
- First, navigate to the Developer tab >> click the Visual Basic button.
Now, this opens the Visual Basic Editor in a new window.
- Second, go to the Insert tab >> select Module.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Sub Remove_Form_Control_Buttons()
Dim FrmCtrl_button As Object
On Error Resume Next
ActiveSheet.Buttons.Delete
For Each FrmCtrl_button In ActiveSheet.OLEObjects
If TypeName(FrmCtrl_button.Object) = "CommandButton" Then
FrmCtrl_button.Delete
End If
Next
End Sub
âš¡ Code Breakdown:
Now, I will explain the VBA code removing Form Controls which is divided into 2 steps.
- In the first portion, the sub-routine is given a name, here it is Remove_Form_Control_Buttons().
- Next, define the variables FrmCtrl_button as Objects.
- In the second potion, use the For Loop and the If statement to remove the objects from the worksheet using the Delete method.
- Third, close the VBA window >> click the Macros button.
This opens the Macros dialog box.
- Following this, select the Remove_Form_Control_Buttons macro >> hit the Run button.
Finally, the results should look like the screenshot given below.
3.2 Removing Form Controls Checkboxes
Considering the Checklist for PC dataset shown in the B4:C13 cells. Here, we have a list of components that are checked if available and unchecked if unavailable. Now, we want to remove the checkboxes present in column C.
📌 Steps:
- Initially, run Steps 1-2 from the previous methods to open the Visual Basic editor, insert a new Module and enter the code.
Sub Remove_Form_Control_Checkboxes()
For Each FrmCtrl_ChBox In ActiveSheet.Shapes
If FrmCtrl_ChBox.Type = msoFormControl Then
If FrmCtrl_ChBox.FormControlType = 1 Then FrmCtrl_ChBox.Delete
End If
Next
End Sub
âš¡ Code Breakdown:
Now, I will explain the VBA code removing Form Controls.
- First, the sub-routine is given a name, here it is Remove_Form_Control_Checkboxes().
- Next, use the For Loop and the If statement to loop through all the cells and remove the checkboxes.
- Following this, close the VBA window >> click the Macros button >> run the Remove_Form_Control_Checkboxes macro.
Consequently, the results should appear in the image given below.
3.3. Removing All Form Controls
Last but not least, you can remove all types of Form Controls objects from your spreadsheet using the VBA code shown in the steps below. So, let’s see it in action.
📌 Steps:
- At the very beginning, navigate to open the Visual Basic editor >> insert a new Module and copy and paste the code.
Sub Remove_All_Objects()
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub
âš¡ Code Breakdown:
Here, I will explain the code for eliminating all Form Controls objects.
- In the first portion, the sub-routine is given a name, here it is Remove_All_Objects().
- Next, use the ActiveSheet.DrawingObjects property to make all the objects visible and then using the Delete method get rid of the objects.
- In turn, exit the VBA window >> press the Macros button >> execute the Remove_All_Objects macro.
Subsequently, your output should look like the picture given below.
Read More:Â How to Create & Apply Option Button Click Event in Excel VBA
How to Delete a Macro Button in Excel
Considering the Project List dataset shown in the B4:C14 cells which contain the Names of the staff and their designated Project Name respectively. Here, we can click the Run Macro button to highlight each project in a different color. In this situation, the steps below will show us how to remove the macro button.
📌 Steps:
- In a similar style, execute Steps 1-2 from the previous methods to open the Visual Basic editor, insert a new Module and enter the VBA code.
Sub Remove_All_Objects()
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub
- Next, close the VBA window >> click the Macros button >> run the Remove_All_Objects macro.
Eventually, this eliminates the macro button as shown in the screenshot below.
Read More: How to Edit a Macro Button in Excel
Practice Section
Last but not the least, we have provided a Practice section on the right side of each sheet so you can practice yourself and please make sure to do it by yourself.
Download Practice Workbook
You can download the practice workbook from the link below.
Conclusion
This article provides the answers on how to remove a form control in Excel in a quick and easy way. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience.
Keep learning and keep growing!
Related Articles
- How to Make a Calculate Button in Excel
- How to Use VBA Code for Submit Button in Excel
- How to Clear Cells in Excel with Button
- How to Add Radio Buttons in Excel
- Radio Button in Excel Without Macro
- How to Group Radio Buttons in Excel
- How to Add Option Button in Excel
- How to Use Option Button in Excel
- How to Create Chart Slider in Excel
- How to Make Games in Excel