Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Count Shapes in a Range (Excel/ VBA)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Shapes in a Range (Excel/ VBA)

    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?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Count Shapes in a Range (Excel/ VBA)

    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

  4. #4
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Count Shapes in a Range (Excel/ VBA)

    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

  5. #5
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Vail, Colorado, USA
    Posts
    194
    Thanks
    36
    Thanked 0 Times in 0 Posts

    Re: Count Shapes in a Range (Excel/ VBA)

    I will try this thanks

    Regards

    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •