Shapes
在 VBE 的对象浏览器中搜索 msoShapeType,您会注意到
有几种形状类型。在你的情况下可能:
msoFormControl (8) - 下拉菜单
msoOLEControlObject (12) - 按钮和东西。
无论如何,请先尝试此代码以确定要删除的内容。
Sub ShapeTypes()
Dim shshape As Shape
Const c1 = " , "
Const r1 = vbCr
Dim str1 As String
str1 = "Shape Types in ActiveSheet"
For Each shshape In ActiveSheet.Shapes
str1 = str1 & r1 & Space(1) & shshape.Name & c1 & shshape.Type
Next
Debug.Print str1
End Sub
以下代码删除 ActiveSheet 上的所有 msoOLEControlObject 类型形状(我假设您要删除):
Sub ShapesDelete()
Dim shshape As Shape
For Each shshape In ActiveSheet.Shapes
If shshape.Type = 12 Then
shshape.Delete
End If
Next
End Sub
最后你的代码:
Sub DeleteShapes()
Const cStrRange As String = "I7:K61"
Const cStrSheet As String = "ADB"
Dim sh As Shape
With Sheets(cStrSheet)
For Each sh In .Shapes
If sh.Type = 12 Then 'or msoOLEControlObject
On Error Resume Next
If Intersect(sh.TopLeftCell, .Range(cStrRange)) Then
If Not Err Then
sh.Delete
End If
End If
End If
Next
End With
End Sub
我仍然没有弄清楚错误背后的原因,但它已被处理并且所有按钮都被删除。
新版本:
Sub DeleteShapes()
Const cStrRange As String = "I7:K61"
Const cStrSheet As String = "ADB"
Dim sh As Shape
With Sheets(cStrSheet)
For Each sh In .Shapes
If sh.Type = 8 Then 'or msoFormControl
On Error Resume Next
If Not Intersect(sh.TopLeftCell, .Range(cStrRange)) Is Nothing Then
If Left(sh.Name,4) = "Butt" then
sh.Delete
End If
End If
End If
Next
End With
End Sub
无需进行错误处理,因为WRONG拦截线导致了错误。