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.
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!"
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!"
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
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
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):
