Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group Shapes
I have a number of shapes on my worksheet. I want to group only SOME
of these. I can fill an array with the names of the shapes I want to group, but how do I tell VBA which shapes to group together? TIA, James |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group Shapes
Hi James,
Perhaps something like this helps (below I'm grouping two shapes, one named 'Shape1' and the other name 'Shape2'): Sub GroupShapes() ActiveSheet.Shapes.Range(Array("Shape1", "Shape2")).Select Selection.ShapeRange.Group.Select End Sub You could replace the array item with your array. "Zone" wrote: I have a number of shapes on my worksheet. I want to group only SOME of these. I can fill an array with the names of the shapes I want to group, but how do I tell VBA which shapes to group together? TIA, James |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group Shapes
Thank you for your reply, Paul. My problem is, I don't know how many
shapes will be affected. What I did was create a dynamic array and redim preserve it as many times as needed to add the shape names to it. ActiveSheet.Shapes.Range(Array(myArray)).select won't work, but I don't know how to put my array in the place of Array("Shape1","Shape2"). This seems to be an array of names, but they're shown explicitly. I could probably get the shapes' index numbers instead, but I don't know if that will help. Any ideas? James Paul Mathews wrote: Hi James, Perhaps something like this helps (below I'm grouping two shapes, one named 'Shape1' and the other name 'Shape2'): Sub GroupShapes() ActiveSheet.Shapes.Range(Array("Shape1", "Shape2")).Select Selection.ShapeRange.Group.Select End Sub You could replace the array item with your array. "Zone" wrote: I have a number of shapes on my worksheet. I want to group only SOME of these. I can fill an array with the names of the shapes I want to group, but how do I tell VBA which shapes to group together? TIA, James |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group Shapes
James,
The array containing the names needs to be an array of variants, not a variant containing an array... Dim MyArray() as Variant Load your array with the particular shape names, then use it like this... Dim shpGroup as Excel.ShapeRange Set shpGroup = ActiveSheet.Shapes.Range(MyArray) shpGroup.Group -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Zone" wrote in message I have a number of shapes on my worksheet. I want to group only SOME of these. I can fill an array with the names of the shapes I want to group, but how do I tell VBA which shapes to group together? TIA, James |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group Shapes
Following is demo code that does a few of the basics. Of note:
1. The dynamic array must be of type Variant for reasons I'm not aware. 2. You can't change the textframe of a group shape while grouped but you can change most other properties. Fill color example shown in the code. You have to ungroup to change the textframe then regroup. 3. Adding text to a rectangle changes it to a textbox. It will behave differently thereafter. Sub Test() Dim s As Shape Dim sr As ShapeRange Dim grp As Shape Dim arr() As Variant 'Must be Variant (don't know why) Dim i As Integer i = 0 'demo adding names of red colored shapes to array For Each s In ActiveSheet.Shapes If s.Fill.ForeColor.RGB = vbRed Then ReDim Preserve arr(i) arr(i) = s.Name i = i + 1 End If Next 'demo grouping shapes named in array Set grp = ActiveSheet.Shapes.Range(arr).Group 'demo changing property of specific group item without ungrouping grp.GroupItems(1).Fill.ForeColor.RGB = vbYellow DoEvents 'demo changing property of entire group object grp.Top = grp.Top + 200 DoEvents 'demo failure of changing textframe of grouped item (raises error) 'grp.GroupItems(1).TextFrame.Characters.Text = "Test 1234" 'demo ungrouping and assigning items to shaperange variable Set sr = grp.Ungroup 'demo iterating through shaperange For i = 1 To sr.Count MsgBox sr(i).Name Next 'demo changing textframe of item when not grouped (succeeds) sr(1).TextFrame.Characters.Text = "Test 1234" 'demo regrouping Set grp = sr.Regroup End Sub Regards, Greg |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Group Shapes
Jim and Greg, Many thanks for your replies. This works great. I was
on the right track and was pretty close, but I tripped over explicitly declaring the array of type Variant, and I needed the Excel.ShapeRange object. Jim, your comments gave me the specific solution to the problem, but both your replies were very helpful to me. Thanks again! Best regards, James Jim Cone wrote: James, The array containing the names needs to be an array of variants, not a variant containing an array... Dim MyArray() as Variant Load your array with the particular shape names, then use it like this... Dim shpGroup as Excel.ShapeRange Set shpGroup = ActiveSheet.Shapes.Range(MyArray) shpGroup.Group -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Zone" wrote in message I have a number of shapes on my worksheet. I want to group only SOME of these. I can fill an array with the names of the shapes I want to group, but how do I tell VBA which shapes to group together? TIA, James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel document with shapes on it but the shapes do not print | Excel Worksheet Functions | |||
Naming Auto Shapes and Creating new Shapes | Excel Discussion (Misc queries) | |||
When drawing shapes in excel the shapes keep disappearing | Excel Discussion (Misc queries) | |||
How can i get more 3D shapes for Auto shapes in excel? | Excel Discussion (Misc queries) | |||
How can i get more 3D shapes for Auto shapes in excel? | Excel Discussion (Misc queries) |