Consulting

Results 1 to 10 of 10

Thread: Excel 2007 VBA: Having trouble ungrouping a newly loaded object

  1. #1

    Excel 2007 VBA: Having trouble ungrouping a newly loaded object

    Hello Excel gurus...

    So in my continual pursuit to create puzzles and games, I have simple WMF file that is comprised of 8 freeform objects.

    What I'm having trouble with is getting the object ungrouped after it's loaded. (Manually ungrouping it works with no problem.)

    I tried

    Set myDocument = Worksheets(1)
    For Each s In myDocument.Shapes
        s.Ungroup
    Next
    And get a "Run-time error '1004': This member can only be accessed for a group." message. But it is a group!

    That code was taken right from the help file.

    After it is ungrouped, I'll need to individually rename each of the 8 components within that group with a loop, based on its location within the grid. That's the easy part.

    Does anyone have any ideas? Thank you in advance for you amazing help.

    Mike

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Does anyone have any ideas? Thank you in advance for you amazing help.
    Put the troublesome shape in a workbook and post it
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3

    Here's a a sample file. - Thank you all

    I am not sure why I didn't do that before... I'm not very bright sometimes...

    Thank you for your help...

    Mike
    Attached Files Attached Files

  4. #4
    Try amending this with the group name, you can see of in the NameBox when you select it.

    ActiveSheet.Shapes.Range(Array("your group name here")).Ungroup
    Hope that helps.
    Get the free Excel based Toolbar or click here for free examples & Excel Consultancy

    RoyUK

  5. #5
    I've just looked at your example and this works for me

    ActiveSheet.Shapes.Range(Array("Group 3")).Ungroup
    Hope that helps.
    Get the free Excel based Toolbar or click here for free examples & Excel Consultancy

    RoyUK

  6. #6
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    344
    Location
    This will convert picture to a group of shapes and then ungroup the shapes

    Sub UngroupPicture()
        ' convert picture to shapes
        ActiveSheet.Shapes.Range(Array(ActiveSheet.Shapes(1).Name)).Ungroup
        ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Ungroup
    End Sub
    Cheers
    Andy

  7. #7
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Ungroup can only be applied to shapes that have been grouped.
    So you will have to test the shapes whether they are grouped or not before applying .ungroup, otherwise the error message will appear.

    Sub M_snb()
       For Each it In Shapes
         If it.Type = 6 Then it.Ungroup
       Next
    End Sub
    Unfortunately in your file you added the grouped shape as a picture, type=13

    In your uploaded file that contains only 1 grouped shape this code suffices
    Sub M_snb()
       For Each it In Shapes
         it.Ungroup
       Next
    End Sub
    or
    Sub M_snb()
       Shapes(1).Ungroup
    End Sub

  8. #8
    Thank you Mr. RoyUK and AndyPope, those actually worked. However, and this part really confuses me - when object is ungrouped, each segment (in this case) is named Freeform X, Freeform, X+1, etc. The problem is, getting this loop to rename what (I assume) should be in each case Freeform 1, Freeform 2, etc.

    Mr. AndyPope: The first line of your code worked perfectly, but the second line of your code:

    ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Ungroup
    gave me the dreaded, but typical 1004, Application defined or object defined error.

    We all know when objects are copied and pasted or are newly created shapes, the internal shape counter ticks away...

    Is there a way to reset that each time (I have to load this image 300 times!) And the number is always different.

    Or, is there a way to get the names of an objects "internal shapes"?

    Thank you both for the awesome code!

    MIke

  9. #9
    MS Excel MVP VBAX Mentor Andy Pope's Avatar
    Joined
    May 2004
    Location
    Essex, England
    Posts
    344
    Location
    Using your example and this modification I get the following information reported in immediate window
    Sub UngroupPicture()
        Dim shp As Shape
        ' convert picture to shapes
        Debug.Print "Original Shapes"
        For Each shp In ActiveSheet.Shapes
            Debug.Print shp.Name
        Next
        ActiveSheet.Shapes.Range(Array(ActiveSheet.Shapes(1).Name)).Ungroup
        Debug.Print "Ungrouped picture"
        For Each shp In ActiveSheet.Shapes
            Debug.Print shp.Name
        Next
        Debug.Print "Ungrouped grouped shape"
        ActiveSheet.Shapes(ActiveSheet.Shapes.Count).Ungroup
        For Each shp In ActiveSheet.Shapes
            Debug.Print shp.Name
        Next
    End Sub
    Original Shapes
    Picture 2
    Ungrouped picture
    Group 3
    Ungrouped grouped shape
    AutoShape 2
    Freeform 4
    Freeform 5
    Freeform 6
    Freeform 7
    Freeform 8
    Freeform 9
    Freeform 10
    Freeform 11


    If you wanted to rename the freeform objects 1 to n then you would need to loop through the shapes once checking for Freeform types, change the name to something unique such as MyFF_nn and then go through the look again rename MyFF_nn to Freeform nn.
    Cheers
    Andy

  10. #10
    Thank you for that AMAZING code! I was so excited to see that! And... It worked! I never would have been able to figure that out, I don't know why.

    You'd think, for someone who's been using Excel since 1990, and VBA since that started (I forget the year,) I'd know more about it.

    But I don't. I do try before I post, I Google, I experiment, because there's no satisfaction greater than being able to figure out something yourself.

    But I'm older now, too and the brain isn't what it used to be...

    So, thank you sir, that was AWESOME!

    Mike

Posting Permissions

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