How do Count how many shapes are on a sheet?

Copper Contributor

Hello everyone, I need help with this issue, How do Count how many shapes are on a sheet? 

13 Replies

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 link 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

 asdas.jpg

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

Hi Jamil,

your code was very useful. thank you very much.

could i ask for some more help regarding the project I am working with? can i send you the excel so that you can see the purpose of the code?

juan,

you are welcome. you can post your excel here if you want to.

Hello again on thank you very much for your help.

 

As you come see we are trying to do some architectural work in an Excel sheet. We were supposed to be using AutoCAD but as we are full supporters of Excel we are trying to do it for the first time in this worksheet.

 

We are trying to measure in each floor how many items are we going to use so that it could be linked to another sheet were prices per item with give us the amount of material used.

 

When we tried to count the shapes it is a bit difficult for us to distinguish how many items per flat we do need. If you look into columns K&L  you can have an idea of what we are trying to measure exactly. I wonder if you could help us to obtain in column M the number of items per floor.

 

All the best!

Hi juan,

I looked at your file and if you have used the built-in Excel shapes or at least your objects were drawings and not picture, then what you have asked was easy.

Now, the complication starts when your objects are not drawing/shapes but they are picture objects and hence we cannot identify them by object type based on how it looks.

If you are willing to work with non-picture objects, then I can write some function that can count based on each room next to the each icon.

Dear friend,

 

I hope you are fine. we have reviewed the excel to assign shapes to all items we would like to count instead of mixing images and shapes. Even though some of them are still marked with an image. The image will serve to guide us from a visual point of view. However the shapes can give us an idea of how many items will have to count.

 

It would be fantastic if we could make a macro that counts the items per floor. We have tried to do the  plans separated so that the surface could be the limited for each floor.

 

In columns K and L you have the shapes that we would like to count.  

 

Please feel free to send us any suggestion that could facilitate the macro to be made. Also if you just make an example for the first floor, we could copy it for the following floor just changing the area to be counted.

 

Thank you very much in advance, Juan

Hi,

I can see that you are only using images. not the original Excel built-in shapes.
However, it is possible to count the specific images, but they need to be named properly.

 

for example these two images have the same name Gráfico 373 & Gráfico 375  if you want them to be counted seperately then you need to name each type differently, regardless of it is number, but the Gráfico to be replaced with something else. similarly for other image objects as well. each of them should have a unique name

dg.jpgas.jpg

 

 

for example this one bulb 1 and bulb 2 for the second image like this and then bulb 3 for third and so on.  so the bulb name should only be used for this image.

as.jpg

Dear Jamil,

Thank you again for your answer and help.

We thought we were using also original shapes.

 

1) How could we assign a shape name to each different one?

2) Which are the "original Excel built-in shapes"?

 

Tx, Juan

 

@Jamil Mohammad Hi, this is very useful for me. I'm wondering how do I list also the color of the different shapes, since I've color coded the same shapes based on color. Thanks in advanced for your help.

@fuad360 

 

Hi,  

 

to retrieve the color, you can use my original code by adding  Cells(nRow, 12) = shp.Fill.BackColor   for background color  and Cells(nRow, 13) = shp.Fill.ForeColor  for front color