Count Shapes in a Range (Excel/ VBA)

Topic

New Reply

I am trying to count the number of shapes in a range and cannot work out the syntax. I can count all the shapes ona sheet but not in a defined range.
this is the start of my code.

aName = ActiveSheet.Name
Set mydocument = Worksheets(aName)
Range(“Test”).Select
Set check = mydocument.Shapes.Range(“Test”).Count
MsgBox check

It fails when I try and set check with runtime error 1004. Test is a defined range on the sheet and has shapes with in it.

thanks

Peter

Reply | Quote

Replies

The line

Set check = mydocument.Shapes.Range(“Test”).Count

makes no sense:

1) Set applies only to object variables, and check appears to be numeric (you want to assign a count to it).
2) The Range property of the Shapes collection returns a subset of shapes, not a cell range.

What exactly do you mean by “shapes in a range”? Shapes entirely contained within the cell range, or shapes overlapping with the cell range?

Reply | Quote

Test is a range of cells some of which have cells aligned exactly within the cell boundary. I am still working on my calendar and each month has 42 cells and of course the maximum no of shapes at least for each day is 31. As I cycle through each month I wanted to know how many shapes were in it so I could loop though them till I found the number (date) I was looking for. I think you second post may well be the answer and I am going to try that. thanks as always

Peter

Reply | Quote

For “entirely contained within”, you can use

Dim aName As String
Dim myDocument As Worksheet
Dim shp As Shape
Dim check As Integer
aName = ActiveSheet.Name
Set myDocument = Worksheets(aName)
For Each shp In myDocument.Shapes
If Not Intersect(myDocument.Range(“test”), shp.TopLeftCell) Is Nothing And _
Not Intersect(myDocument.Range(“test”), shp.BottomRightCell) Is Nothing Then
check = check + 1
End If
Next shp
MsgBox check

Reply | Quote

I will try this thanks

Regards

Peter

Reply | Quote