Excel

Delete all ActiveX Controls from a sheet

Ease of Use

Easy

Version tested with

2000, 2002 

Submitted by:

lucas

Description:

Allows you to delete all ActiveX Controls from the activesheet. 

Discussion:

Occationally you wind up with a sheet littered with activeX controls. Comboboxes, etc and you wish to remove them all. Use this script to accomplish that task with the understanding that it does not work on controls created from the forms toolbar. There is alternative code in the module for removing specific controls. The exampe shows how to remove just the ActiveX comboboxes from the active sheet. 

Code:

instructions for use 

			
Put this code In a standard module: 
 
Option Explicit 
 
Sub DeleteAllControls() 
    Dim obj As OLEObject 
     'Selects all ActiveX controls on the active sheet and deletes them
    For Each obj In ActiveSheet.OLEObjects 
        obj.Delete 
    Next obj 
End Sub 
 
 'If you wish to delete the controls selectively
 'This example will allow you to delete only the ActiveX Comboboxes on the sheet
Sub DeleteComboboxesOnly() 
    Dim obj As OLEObject 
     'Checks each ActiveX control on the active sheet
     'If the control is a ComboBox delete it
     'go to the next control and do same.
    For Each obj In ActiveSheet.OLEObjects 
        If obj.ProgId = "Forms.ComboBox.1" Then obj.Delete 
    Next obj 
End Sub

How to use:

  1. Open the Visual Basic Editor by going to tools-Macro's-Visual Basic Editor or use Alt-F11
  2. On the toolbar of the Visual Basic Editor, go to insert - module
  3. In the module pane paste the code above.
  4. Close the Visual Basic Editor by clicking the X in the upper right corner or go to File-Close
 

Test the code:

  1. Place several controls on your worksheet from the controls toolbox.
  2. From the main menu select tools-Macro-Macros
  3. From the macros dialog box select the DeleteAllControls macro and click on Run
  4. If you wish to only remove comboboxes, select the DeleteComboboxesOnly macro.
 

Sample File:

Delete_ActiveX_Controls.zip 22.12KB 

Approved by mdmackillop


This entry has been viewed 126 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express