How to check whether any shape exists?

Question

How to check if any shape exists in sheet?

I have used below code:

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

As I have not given shape name, it is executing 'else' part of 'If-else' loop.

I can't give shape name here as each time shape name is different.

Answer 1

Check if any Shapes exist

If you want to check whether there are any VBA Shapes on the active worksheet then you can simply check the value of the .Count property of the Shapes object:

ActiveSheet.Shapes.Count

...which will return the number of shapes, or return zero if there are no shapes.

Example Usage:

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

---

Check if specific Shape exists

If you need to check whether a specific shape exists, use this function:

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

Example usage:

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

---

List All Shapes

Lists all shapes on the active worksheet, in the Immediate window. (Hit Ctrl+G to open it.)

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

---

Delete All Shapes

Deletes all shapes from the active worksheet.

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

---

More Information:

For more detailed information and examples of working with VBA Shapes, see my answers to other Shape-related questions (including controls which are just another kind of Shape):

• 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