excel - 如何检查是否存在任何形状？

Question

如何检查sheet中是否存在形状？

我使用了以下代码:

Sub my()
    Dim shp As Shape
    If Not shp Is Nothing Then
        For Each shp In Sheet1.Shapes
            mesage = shp.TopLeftCell.Address(0, 0)
        Next shp
    Else
        mesage = Sheet1.Cells(1, 12).Address
    End If

End Sub

由于我没有给出形状名称，因此它正在执行“If-else”循环的“else”部分。

我无法在这里给出形状名称，因为每次形状名称都不同。

Answer 1

检查是否存在任何形状

如果你想检查是否有任何VBA Shapes在事件工作表上，您可以简单地检查 .Count property 的值Shapes object的:

ActiveSheet.Shapes.Count

...它将返回形状的数量，或者如果没有形状则返回零。

用法示例:

If ActiveSheet.Shapes.Count = 0 Then MsgBox "No shapes found!"

<小时/>

检查特定形状是否存在

如果您需要检查特定形状是否存在，请使用此函数:

Function shapeExists(shapeName As String) As Boolean
'returns TRUE if a shape named [ShapeName] exists on the active worksheet
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
         If sh.Name = shapeName Then shapeExists = True
    Next sh
End Function

用法示例:

If Not shapeExists("My Shape Name") Then MsgBox "Shape not found!"

<小时/>

列出所有形状

在立即窗口中列出事件工作表上的所有形状。 (按 Ctrl+G 打开它。)

Sub ListAllShapes()
'list all shapes on the active worksheet
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        Debug.Print "id=" & sh.ID, "name=" & sh.Name
    Next sh
End Sub

<小时/>

删除所有形状

从事件工作表中删除所有形状。

Sub DeleteAllShapes()
'delete all shapes on the active worksheet (Including CONTROLS, so use with caution!)
    Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
        sh.Delete
    Next sh
End Sub

<小时/>

更多信息:

有关使用 VBA 形状的更多详细信息和示例，请参阅我对其他形状相关问题的回答(包括控件，它们只是另一种形状):

• Pasted Shape not seen as “Latest” Shape
• Changing arc length in excel based on a cell value
• Can't add items to a combobox shape
• Overview of differences between Form Controls and ActiveX Controls in Excel
• How to use Events with Option Button Controls on UserForm
• How to disable right click menu of shapes