How to Remove a Form Control in Excel (3 Quick Ways)

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.


Download Practice Workbook

You can download the practice workbook from the link below.


3 Ways to Remove a Form Control in Excel

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.

how to remove a form control in excel

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.

Using Find and Select Option

Now, this opens the Go To Special wizard.

  • Next, choose Objects >> hit the OK button.

Go To Special Window

  • In turn, this selects all the objects in the worksheet >> press the DELETE key on your keyboard.

Selecting Form Controls

Finally, this eliminates the Scroll Bar as shown in the image below.

how to remove a form control in excel with Find & Select option


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.

Using Keyboard Shortcut

  • Then, choose Objects >> click the OK button.

Select Objects

  • Following this, press the DELETE key to remove the Form Control object.

Selecting Scroll Bar

Eventually, your output should look like the picture given below.

how to remove a form control in excel with keyboard shortcut


Similar Readings


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.

Applying VBA Code


3.1 Removing Form Controls Buttons

Now, the following steps will show us how to get rid of the button using the VBA code.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Removing Form Controls Buttons

Now, this opens the Visual Basic Editor in a new window.

  • Second, go to the Insert tab >> select Module.

Inserting 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

Inserted VBA Code

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.

how to remove a form control in excel

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

Running VBA Code

Finally, the results should look like the screenshot given below.

how to remove a form control in excel with VBA Code


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.

Removing Form Controls Checkboxes

📌 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

Inserted Code into Module

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.

how to remove a form control in excel

  • Following this, close the VBA window >> click the Macros button >> run the Remove_Form_Control_Checkboxes macro.

Running the macro

Consequently, the results should appear in the image given below.

how to remove a form control in excel with VBA


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

VBA Code in the Module window

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.

how to remove a form control in excel

  • In turn, exit the VBA window >> press the Macros button >> execute the Remove_All_Objects macro.

Running macro to remove all objects

Subsequently, your output should look like the picture given below.

how to remove a form control in excel to remove all form controls

Read More: Excel VBA: Form Control Checkbox Value (3 Examples)


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.

How to Delete a Macro Button in Excel

📌 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

code for removing macro button

  • Next, close the VBA window >> click the Macros button >> run the Remove_All_Objects macro.

executing macro

Eventually, this eliminates the macro button as shown in the screenshot below.

how to remove a form control in excel

Read More: How to Edit a Macro Button in Excel (5 Easy Methods)


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.

Practice Section


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. We, the Exceldemy team, are happy to answer your queries. Keep learning and keep growing!


Related Articles

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo