VBA to do stuff with shapes many of which belong to groups.

[Engineer Joe]

Ok, so here's the briefing: I wrote some vba that is supposed to adjust the height and width of some textboxes saved in a template worksheet that will later be applied as needed with a userform tool. Here's the code I wrote:

For Each shp In ThisWorkbook.Sheets("FORMS TEMPLATE").Shapes
    If shp.name Like "TB*" And (shp.name Like "*UP" Or shp.name Like "*LEFT" Or shp.name Like "*RIGHT" Or shp.name Like "*DOWN") Then
        shp.Width = ThisWorkbook.Sheets("FORMS TEMPLATE").Range("TB_ACTUAL_SIZE").Width
        shp.Height = ThisWorkbook.Sheets("FORMS TEMPLATE").Range("TB_ACTUAL_SIZE").Height

    End If
Next shp

Aside: I've noticed that sometimes after copying objects or clicking some buttons in excel, the objects/controls change sizes a tiny bit, but over time they can change alot. That's why this code above exists. Plus, I can resize every template textbox by referring to range dimensions which I know will not change unless I directly modify them (which may also be done by code! yay!)

So anyway, when I executed the vba script, i noticed that the objects that lay within groups are no longer (apparently) members of the shapes collection. I've been working in VBA for like 10 years now, but shapes and things related to shapes are not my strong suit. What's the best way to get a handle on the text boxes that are actually a mixture of a textbox and an arrow that I've grouped and named? I decided on the textbox + arrow before I realized how much a pain in the *** the grouping stuff was going to be. Now, I'm just about at the point where I want to switch to actual "callouts" in my templates, but I haven't messed with them enough to know if they might be more trouble than my own hodgepodge "custom callout" composed of a textbox and a single line arrow connected at one of the four nodes (positions) that I then group and name accordingly. I'm at a point where I need to make a decision, and I'd like two things if you'd be so generous to offer either:

1) A general recommendation about whether I should just switch to callouts instead of my own custom creations. I like that my TxtBx + Arrow groups actually have arrows because I think it looks better graphically. And i'm not seeing that option in Callouts. But if I can just build a template of callouts and it''s easier to reference them programatically, perhaps that's the solution. Anyone out there with much VBA experience in shapes, I'd love for you to chime in.

2) IF the best solution is to stick with my own little bastardized callouts of Grouped(TxtBox+Line with arrow), how do I change those shapes in a group without disassembling the group? Or can I modify them while they're still in the group? If so, how? Also, this could grow to dozens of shapes, so referencing them by name is not a long term option. Right now there are 16. A year from now, this could easily be 100 template shapes in a template worksheet which I'd like to be able to modify at whim.

 

[Norie]

You can modify the individual shapes within a group with out ungrouping/disassembling.

Here's some very simple example code that doesn't actually do much but it does show you how to access the shapes in a group.

Dim grp As Shape
Dim shp As Shape

    Set grp = ActiveSheet.Shapes("Group 3")
    
    For Each shp In grp.GroupItems
        MsgBox shp.Name
    Next shp
    
    
    Set shp = grp.GroupItems("TextBox 1")
    
    shp.TextFrame.Characters.Text = "This is a shape in a group"

 

[Engineer Joe]

NORIE!!!!!!!!!!!!!!!!!!!!! Hey, it's been a long time since I've been really active on the board, but over the years, you have answered so many of my questions! Thank you so much for all of your efforts that go unrewarded except for the appreciation! So, thank you!!!!! Now, from what you just wrote, It looks like I can write a "for each" group maybe? If not, I can change the naming convention of my groups to include the word "group", and if I do that, I can do a "for each" on my shapes and then an "if shp.name like grp" then i can nest another "for each shp in grop.GroupItems" ...if shp.name like <insert a modified version of my code above>. I'll get to looking. Thanks again.

 

[Norie]

You can call the grouped items whatever you want - I was just using the default name that Excel used when I grouped a couple of shapes.

 

[Engineer Joe]

For the sake of posterity, I'm posting the final vba. As it turns out, all of my groups are named with the prefix "CO" for callout, so it was a pretty simple fix once I integrated your example. Thanks again, Norie.

For Each shp In ThisWorkbook.Sheets("FORMS TEMPLATE").Shapes
    
    If Left(shp.Name, 2) = "CO" Then
        
         For Each shp2 In shp.GroupItems
            If shp2.Name Like "TB*" And (shp2.Name Like "*UP" Or shp2.Name Like "*LEFT" Or shp2.Name Like "*RIGHT" Or shp2.Name Like "*DOWN") Then
            shp2.Width = ThisWorkbook.Sheets("FORMS TEMPLATE").Range("TB_ACTUAL_SIZE").Width
            shp2.Height = ThisWorkbook.Sheets("FORMS TEMPLATE").Range("TB_ACTUAL_SIZE").Height
            End If
         Next shp2

 
    End If
    
Next shp