How to Delete an ActiveX Control in Excel Using VBA
Question details
The user wants to know how to programmatically remove an ActiveX button or other ActiveX controls from an Excel worksheet using a VBA macro.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Developing or maintaining an Excel macro where specific or all ActiveX controls need to be removed, sometimes requiring the worksheet to be unprotected or copied first.
- Observed behavior
- The user needs the correct VBA syntax and object references (such as iterating through the Shapes collection and identifying msoOLEControlObject types) to successfully delete these specific controls.
Before running any macros, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled Developer options to access the VBA editor. Always test structural macros on a backup copy of your file.
Delete All ActiveX Controls on a Worksheet
Use a VBA loop to identify and remove all ActiveX controls (OLE objects) from your active sheet.
ActiveX controls are recognized by Excel VBA as shapes with the type 'msoOLEControlObject'. By looping through the Shapes collection on a specific worksheet, you can identify and delete these controls without affecting standard shapes, charts, or images.
Press 'Alt + F11' on your keyboard to open the Microsoft Visual Basic for Applications (VBA) editor.
Click 'Insert' in the top menu and select 'Module' to create a blank script window.
Copy and paste the following code into the module window: Sub DeleteAllActiveX() Dim shp As Shape For Each shp In ActiveSheet.Shapes If shp.Type = msoOLEControlObject Then shp.Delete End If Next shp End Sub
Press 'F5' or click the 'Run' button (the green play triangle) in the toolbar to execute the macro and delete all ActiveX controls on the currently active sheet.
Delete a Specific ActiveX Control by Name
Target a single ActiveX control, such as a specific command button, without affecting other controls.
Copy Worksheet Before Deleting Controls
Create a copy of the original worksheet and remove the ActiveX controls from the new copy to preserve the original data.
Run and Edit VBA Macros Seamlessly with WPS Office
WPS Spreadsheet provides excellent compatibility with Microsoft Excel macros, allowing you to run, edit, and troubleshoot VBA code—including ActiveX control management—without modifying your existing scripts.
- 1. Open Your Macro Workbook: Launch WPS Spreadsheet and open your existing macro-enabled Excel workbook (.xlsm).
- 2. Access the Developer Tab: Navigate to the Developer tab on the top ribbon and click on 'VBA Editor' to open the coding environment.
- 3. Edit Your Script: Insert or modify your ActiveX deletion macro code exactly as you would in standard Microsoft Excel.
- 4. Run and Save: Run the macro to remove the desired controls, then save your workbook seamlessly.
Frequently Asked Questions
What is msoOLEControlObject in VBA?
The `msoOLEControlObject` is a built-in Excel VBA constant used to identify ActiveX controls (like command buttons, combo boxes, or text boxes) within a worksheet's Shapes collection. It allows your macro to distinguish ActiveX objects from standard shapes or Form Controls.
Why didn't my ActiveX control delete when using Shapes.Delete?
If you simply use `ActiveSheet.Shapes.SelectAll` and hit delete, or if you don't specify the correct object type in a loop, Excel might miss ActiveX objects depending on the context. Filtering by `shp.Type = msoOLEControlObject` ensures the macro explicitly targets ActiveX controls.
Can I undo a VBA macro execution?
No, actions performed by a VBA macro usually clear the Excel undo stack. You cannot use 'Ctrl + Z' to restore deleted ActiveX controls. It is always recommended to save a backup of your workbook before running macros that delete objects or alter sheet structures.
