Forum Discussion

13 Replies

Sort By
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Nov 16, 2018

    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

    • Jamil's avatar
      Jamil
      Bronze Contributor
      Nov 16, 2018

      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

       

      • juan jimenez's avatar
        juan jimenez
        Iron Contributor
        Nov 19, 2018

        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

Resources