Nov 16 2018 02:50 AM
Hello everyone, I need help with this issue, How do Count how many shapes are on a sheet?
Nov 16 2018 05:37 AM
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
Nov 16 2018 06:33 AM
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
Nov 19 2018 01:04 AM
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
Nov 19 2018 02:12 AM - edited Nov 19 2018 02:13 AM
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
Nov 20 2018 12:36 AM
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?
Nov 20 2018 02:04 PM
Nov 21 2018 08:56 AM
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!
Nov 21 2018 10:19 AM
Nov 29 2018 02:06 AM
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
Nov 30 2018 01:48 AM
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
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.
Nov 30 2018 03:08 AM
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
Aug 26 2020 01:07 AM
@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.
Aug 28 2020 06:30 AM - edited Aug 28 2020 06:30 AM
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