删除Excel工作表中除窗体控件以外的所有形状 [英] Delete all shapes in excel worksheet except form controls

问题描述

我有一个excel工作表，其中与按钮相关联的宏根据工作表中的用户输入参数绘制动态形状.

I have an excel worksheet where a macro tied to a button draws dynamic shapes based on the user input parameters in the worksheet.

我试图编写一个新的宏来清理工作表，换句话说，删除工作表中的所有形状.

I am trying to write a new macro to clean the sheet, or in other words delete all shapes in the worksheet.

我尝试使用下面的代码，它确实删除了所有形状，但是在此过程中按钮窗体控件也被删除了.是否有一种简单的方法可以仅消除工作表中的形状(箭头，文本框，椭圆形等)?谢谢一大堆！

I tried using the code below, and it indeed deletes all shapes, however button form controls also get deleted in the process. Is there an easy way to only get rid of the shapes (arrows, textboxes, ellipses etc.) in the worksheet? Thanks a bunch!!!

Sub DeleteAllShapes()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    Shp.Delete
Next Shp

End Sub

推荐答案

仅删除自动形状和文本框，可以使用:

To delete autoshapes and textboxes only you can use:

Sub DeleteAllShapes()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoAutoShape Or Shp.Type = msoTextBox Then Shp.Delete
Next Shp

End Sub

或者，您也可以采用其他方法，并指定不删除的类型.您可以使用枚举类型，但使用类型名称更容易理解.以下代码段将删除除表单控件"和"OLE控件"对象之外的所有内容.

Alternatively you can work the other way around and specify the types not to delete. You can use the enumerated types but it's more readable to use the type names. The following snippet will delete everything apart from Form Controls and OLE control objects.

Sub DeleteAllShapes()

Dim Shp As Shape

For Each Shp In ActiveSheet.Shapes
    If Not (Shp.Type = msoOLEControlObject Or Shp.Type = msoFormControl) Then Shp.Delete
Next Shp

End Sub

MSO形状类型的完整列表. http://msdn.microsoft.com/zh-cn/library/office/aa432678(v = office.12).aspx

A full list of MSO Shape Types. http://msdn.microsoft.com/en-us/library/office/aa432678(v=office.12).aspx

罗恩·德·布鲁因(Ron de Bruin)有大量的摘要，这些摘要可能与遇到此问题的其他人有关. http://www.rondebruin.nl/controlsobjectsworksheet.htm

Ron de Bruin has a good collection of snippets which may be relevant to anyone else coming across this question. http://www.rondebruin.nl/controlsobjectsworksheet.htm

这篇关于删除Excel工作表中除窗体控件以外的所有形状的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！