+ Reply to Thread
Results 1 to 12 of 12

Shapes.Range(Array(...))

  1. #1
    Registered User
    Join Date
    11-01-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Shapes.Range(Array(...))

    We start with some Excel 2007 VBA to draw a shape of several parts, and to group those parts. Somewhat condensed for legibility, the code so far looks like:

    Please Login or Register  to view this content.
    So far, so good.

    Then
    Please Login or Register  to view this content.
    That works fine (even if I can't explain why it has a lower-case "s" on "shapes"). But then I run the code again and Excel reports
    > Run-time error '1004':
    > Grouping is disabled for the selected shapes.

    My guess is that Excel 2007 is attempting to group the first-made items with names "Part1" and "Part2", which are already part of a group. Hence the failure.

    Please, is it possible to specify the Shapes.Range(Array(...)) using the shape pointers (sh1 and sh2) rather than the names?

    Thank you.
    Last edited by jdaw1_SG; 11-03-2011 at 04:05 AM. Reason: shapes, range, array, name

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shapes.Range(Array(...))

    You're probably right, can't name multiple shapes the same thing. Do you need to? Maybe:

    Please Login or Register  to view this content.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    11-01-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Shapes.Range(Array(...))

    Thank you, but I had tried that, unsuccessfully.

    Is there a way to get an object's index/position number within its parent?

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shapes.Range(Array(...))

    I just tried it and it worked. What is failing for you?

  5. #5
    Registered User
    Join Date
    11-01-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Shapes.Range(Array(...))

    Does it work for you if you run it a second time?

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shapes.Range(Array(...))

    Yes, of course. My version creates another grouped set on top of the first, but move them out of the way and you'll see the new ones. This is an example, by NOT naming them, the macro will continue to work because it is attaching the sh1 and sh2 variable to the randomly named shapes as they are created, so it should work every time.

    You just need to work out the rest of the undisclosed parameters... my macro shows a full example, yours did not.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Shapes.Range(Array(...))

    Quote Originally Posted by jdaw1_SG View Post
    Does it work for you if you run it a second time?
    Are you running the whole code twice (i.e. recreating shapes each time), or just the group bit?
    Remember what the dormouse said
    Feed your head

  8. #8
    Registered User
    Join Date
    11-01-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Shapes.Range(Array(...))

    Thank you. That shows the solution.

    Please Login or Register  to view this content.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Shapes.Range(Array(...))

    You're completely ignoring my suggestion as a whole. I set the sh1 and sh2 variables at the same moment the variables were created, so VBA already knows what they're called. So there is no need to "name" them at all, which appears to be what you are still struggling with.

    Anyway, my approach works, but if you've got your own, that's good, too.

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  10. #10
    Registered User
    Join Date
    11-01-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Shapes.Range(Array(...))

    Marked solved, I hope. Thank you.

    My preference is that names be useful to humans. So that, if taking something apart much later, it is clear what is what. For that reason I don't like machine-generated random names, even if uniqueness is guaranteed. So I'm naming, manually, as a sensible names suffixed with
    Please Login or Register  to view this content.
    I agree that it would be neater to do that at the instant of creation, rather than a few lines later. Presumably for a “.AddShape(Type:=msoShapeRectangle, ...)”, I could add a “Name=”. Can something similar be done for a “.AddPolyline(...)”?

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Shapes.Range(Array(...))

    Yes, since it returns a Shape object too.

  12. #12
    Registered User
    Join Date
    11-01-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Shapes.Range(Array(...))

    So “.AddPolyline(...).Name="Piece" & suffix ”?

    That’s easy: thank you again.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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