Group a number of Freeforms

[jinxtt]

Hi,

I am writing a programme which creates a number of freeforms depending on user inputs. I have used the BuildFreeForm to create these shapes. I would like to group all the shapes together and flip, and I was wondering what the best way is to do this.

My ideas are to either add the newly formed shapes to a collection then flip once all shapes have been added, or to select all shapes which are of the type freeform at the end and then flip.

Either way, I'm not quite sure how to write the code. If anyone can help this would be much appreciated.

Many thanks in advance.
Jinxtt

[jinxtt]

Here's some code to go with my earlier post. VBA doesn't like the second line which I determined using the macro recorder. I'm not sure VBA is actually selecting all of the freeforms as per the first line either.

If anyone has any ideas as to how I can select all freeforms on the sheet and flip them, please let me know.

Please Login or Register  to view this content.

[Andy Pope]

This code will create 3 shapes and then individually flip horizontally before grouping and flipping vertically.
Step through the code to see the changes.

Please Login or Register  to view this content.

[jinxtt]

Thanks for your input Andy. The problem is that I cannot flip each shape individually, because their relative positions will change. I need to group all the shapes first then flip the group. The shapes in question are all freeforms, so I need to group all freeforms on the sheet (there are other shapes on the sheet which are not freeforms and must not be grouped), and flip vertically. The code I posted previously is my attempt at this.

This sounds like an easy thing to do but I just can't seem to get the code right. I've used the help relating to the Shapes Collection Object on MSDN, but I still can't figure it out.

Thanks
J

[Andy Pope]

Not sure I follow. My code shows both ways, flipping horizontally indiviual shapes. Also grouping shapes and flipping that vertically.

Just use the section of code that's relevant to your problem

[jinxtt]

Doesn't it assume that you know the number of shapes there are i.e. each shape has an index number. In my case there could be 5 shapes or there could be 20 shapes depending on the user input. Is there a way of selecting all freeforms on the sheet and then grouping, as you would if you did it manually in excel, that isn't dependent on indexing?

[Andy Pope]

You just need to replace the static number in my example with the count of shapes on the sheet.
You can even add code to the loop to exclude shapes if you have some criteria upon which to exclude.

Please Login or Register  to view this content.

[jinxtt]

That's very impressive Andy. Instead of excluding shapes, can I count just the freeforms i.e. focus the shapes object down to the freeforms only. This would be perfect for my requirements.

For lngIndex = 1 To ActiveSheet.Shapes(msoFreeForm).Count  

[Andy Pope]

Please Login or Register  to view this content.

Note once flipped the shapes are ungrouped

[jinxtt]

You are very talented at this - many thanks for your help!

[jinxtt]

Hi Andy,

I've got all of the above to work nicely on my home PC which runs Excel 2007. However the spreadsheet I'm creating is for work where the company runs Excel 2003. When I run the above code I get error 13 (Type mismatch), and it refers specifically to the following line of code:

Set shpGroup = ActiveSheet.Shapes.Range(Split(Left(strNames, Len(strNames) - 1), ",")).Group

I've looked into it and it appears to be having dificulty running the split function element of the code. I looked in the help file and the Split function appears to be the same in both versions of Excel. Any ideas what could be causing the problem?

I've made a couple of minor changes to the code (below is the code I'm using).

Thanks
J

 
    Dim section As Worksheet 
    Set section = Worksheets(1) 
    Dim FFGroup As Shape 
    Dim lngIndex As Long 
    Dim strNames As String 
     
    For lngIndex = 1 To section.Shapes.Count 
        If section.Shapes(lngIndex).Type = msoFreeform Then 
            strNames = strNames & section.Shapes(lngIndex).Name & "," 
        End If 
    Next 
    If Len(strNames) > 0 Then 
        Set FFGroup = section.Shapes.Range(Split(Left(strNames, Len(strNames) - 1), ",")).Group 
        FFGroup.Flip msoFlipVertical 
        FFGroup.Ungroup 
    End If  

[Andy Pope]

It's not the split function but rather the array it returns.

Instead you need to build the array

Please Login or Register  to view this content.

[jinxtt]

Andy, I'm getting the same type mismatch error, with the following line of code highlighted:

Set FFGroup = section.Shapes.Range(vntArrayNames)

I've hovered the cursor over all the code to see if anything is amiss and I've noticed that I get "subscript out of range" when hover over vntArrayNames in the following line of code:

vntArrayNames(lngArrayIndex) = section.Shapes(lngIndex).Name

[Andy Pope]

The code works for me in a test file

[jinxtt]

OK got it. I didn't notice that you had made FFGroup a shaperange rather than a shape, and when I think about it, its perfectly clear why.

Many thanks for all your help.

J