+ Reply to Thread
Results 1 to 9 of 9

Grouping Shapes

  1. #1
    Registered User
    Join Date
    01-09-2006
    Posts
    16

    Grouping Shapes

    I am trying to group all shapes on a sheet with the fiollowing code and getting the error:

    "The Index into the specified collection is out of bounds"

    Private Sub CommandButton1_Click()

    Dim MyObject As OLEObject
    Dim myobjectname As String
    Dim myArr() As String

    counter = -1

    For Each MyObject In Sheets("Exhibit").OLEObjects
    counter = counter + 1
    ReDim Preserve myArr(0 To counter)
    myArr(counter) = MyObject.Name
    Next

    ActiveSheet.Shapes.Range(Array(myArr)).Select
    Selection.ShapeRange.Group.Select

    End Sub

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Completely untested, Lara, try this:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-09-2006
    Posts
    16
    now I get the error:

    The specified parameter has an invalid value

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    Interesting; it really wants a variant:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-09-2006
    Posts
    16
    Great, thanks! OK, now one more little hitch, I also have a picture on the sheet with a constant name, say "Picture1". This is not being included in the grouping since it is not an Ole Object. Is there any way to tack that onto the array after it loops through all the objects. I tried
    avName = avName & "Picture1"
    but that does not work.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    No, that won't work; you'd have to resize the array and add it.

  7. #7
    Registered User
    Join Date
    01-09-2006
    Posts
    16
    Hmmm, I am afraid that is unfamiliar to me, what is the code to resize an array and add the picture?

  8. #8
    Registered User
    Join Date
    01-09-2006
    Posts
    16
    ok, I have been trying some different code, but still running into issues, here is what I have now for adding that extra picture in and I am getting the error:
    "The item with the specified name wasn't found"

    Dim oOLE As OLEObject
    Dim avName() As Variant
    Dim iCtr As Long
    Dim iCtrNew As Long

    For Each oOLE In Sheet1.OLEObjects
    iCtr = iCtr + 1
    ReDim Preserve avName(1 To iCtr)
    avName(iCtr) = oOLE .Name
    Next

    iCtrNew = iCtr + 1
    ReDim Preserve avName(1 To iCtrNew)
    avName(iCtrNew) = avName(iCtr) & "Picture1"

    ActiveSheet.Shapes.Range(avName).Select
    Selection.ShapeRange.Group.Select

  9. #9
    Registered User
    Join Date
    01-09-2006
    Posts
    16

    Smile

    I Got it!! Wow, I was so close before. shg, thanks for all your help. Here is my final code that works:

    Dim oOLE As OLEObject
    Dim avName() As Variant
    Dim iCtr As Long
    Dim iCtrNew As Long

    For Each oOLE In Sheet1.OLEObjects
    iCtr = iCtr + 1
    ReDim Preserve avName(1 To iCtr)
    avName(iCtr) = oOLE .Name
    Next

    iCtrNew = iCtr + 1
    ReDim Preserve avName(1 To iCtrNew)
    avName(iCtrNew) = "Picture1"

    ActiveSheet.Shapes.Range(avName).Select
    Selection.ShapeRange.Group.Select

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1