Forum Discussion
How do Count how many shapes are on a sheet?
Hi,
I think the only way to do that is by using a VBA code!
So, please try the following code to get the count of all shapes in the active worksheet so that it's displayed in a message box.
Sub GetShapesCount()
MsgBox ActiveSheet.Shapes.Count
End Sub
Please follow this https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/ to learn how to insert and run this code.
Hope that helps
Shapes can be charts, callouts, diagrams many types of shapes exists.
if you want to count particular one of them then you can use the code below and you can change the shp.type on any of the below listed ShapeType Enumeration to count particular ones or exclude or include shape types. if you remove the shape type then it will count anything that is classified as shapes in Excel
Sub Test2()
Dim shp As Shape
For Each shp In Sheet1.Shapes
If shp.Type = 1 Then
Counter = shp.ID + 1
End If
Next
MsgBox Counter - 2
End Sub
Thank you very much for your answers.
In my case I am using 10 different shapes in a sheet that are repeated several times each one.
I would like to count at the end of the sheet how many of them I have you all over sheet.
However, please note that some are lines of different desing, different circles or rectangles or basic forms.
Looking forward to hearing from you. best regards, Juan
You can use the code below and it will add new sheet and will list all of the shapes with their name and location, then you can pivot it to see how many of which you have.
Sub ListShapes() Dim Wks As Worksheet Dim shp As Shape Dim nRow As Long Sheets.Add Cells.Clear Cells(1, 1) = "Worksheet" Cells(1, 2) = "Shape" Cells(1, 3) = "Type" Cells(1, 4) = "OnAction" Cells(1, 5) = "Hyperlink" Cells(1, 6) = "TopLeft" Cells(1, 7) = "BotRight" Cells(1, 8) = "Height" Cells(1, 9) = "Width" Cells(1, 10) = "Autoshape" Cells(1, 11) = "Form" nRow = 1 On Error Resume Next For Each Wks In ActiveWorkbook.Worksheets For Each shp In Wks.Shapes nRow = nRow + 1 Cells(nRow, 1) = "'" & Wks.Name Cells(nRow, 2) = shp.Name Cells(nRow, 3) = shp.Type Cells(nRow, 4) = shp.OnAction Cells(nRow, 5) = shp.Hyperlink.Address Cells(nRow, 6) = shp.TopLeftCell.Address(0, 0) Cells(nRow, 7) = shp.BottomRightCell.Address(0, 0) Cells(nRow, 8) = shp.Height Cells(nRow, 9) = shp.Width Cells(nRow, 10) = shp.AutoShapeType Next shp Next Wks done: nRow = nRow + 0 End Sub
