Reference Grouped Objects Using VBA

B

BJ

Hello

I have grouped four charts, three text boxes (from the control toolbox)
and a rectangle. Prior to grouping, the code I had to update the axes
on both charts and text for the text boxes worked fine. After grouping
the objects the code fails.
How do you reference a specific object contained in a group?
 
J

Jim Cone

If Shp is a grouped shape then
Shp.GroupItems(2) references the second shape in the group
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
..

"BJ" <[email protected]>
wrote in message
Hello
I have grouped four charts, three text boxes (from the control toolbox)
and a rectangle. Prior to grouping, the code I had to update the axes
on both charts and text for the text boxes worked fine. After grouping
the objects the code fails.
How do you reference a specific object contained in a group?
 
B

BJ

Jim said:
If Shp is a grouped shape then
Shp.GroupItems(2) references the second shape in the group
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
.

"BJ" <[email protected]>
wrote in message
Hello
I have grouped four charts, three text boxes (from the control toolbox)
and a rectangle. Prior to grouping, the code I had to update the axes
on both charts and text for the text boxes worked fine. After grouping
the objects the code fails.
How do you reference a specific object contained in a group?

Thanks for your reply Jim.

I tried using your example but I keep getting errors. Sorry, my syntax
is more than likely incorrect since I haven't written code referencing
grouped objects until now.

Could you please provide a code example showing how I could reference
the two charts in the grouping?
Or, is there a way to display in a message box what groupings are on a
worksheet and then each object and its name or reference within that
grouping?
 
J

Jim Cone

The group must be ungrouped, in most cases, in order
to do anything to a group member. Something like this maybe...

Sub GroupedStuffOnWorksheet
Dim Shp As Excel.Shape
Dim shpRng As Excel.ShapeRange
Dim arrShps() As Variant 'required
Dim x As Long
Dim N As Long

For Each Shp In ActiveSheet.Shapes
If Shp.Type = msoGroup Then
x = Shp.GroupItems.Count
ReDim arrShps(1 To x)
For N = 1 To x
arrShps(N) = Shp.GroupItems(N).Name
Next
Shp.Ungroup
Set shpRng = ActiveSheet.Shapes.Range(arrShps)

For N = 1 To x
'See VBA help for "Type" Property
If shpRng(N).Type = msoChart Then
'....do something
End If
Next
Shp.Regroup 'or shpRng.Regroup
End If
Next
End Sub
--------
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html


"BJ" <[email protected]>
wrote in message
Thanks for your reply Jim.
I tried using your example but I keep getting errors. Sorry, my syntax
is more than likely incorrect since I haven't written code referencing
grouped objects until now.
Could you please provide a code example showing how I could reference
the two charts in the grouping?
Or, is there a way to display in a message box what groupings are on a
worksheet and then each object and its name or reference within that
grouping?
 
B

BJ

Jim said:
The group must be ungrouped, in most cases, in order
to do anything to a group member. Something like this maybe...

Sub GroupedStuffOnWorksheet
Dim Shp As Excel.Shape
Dim shpRng As Excel.ShapeRange
Dim arrShps() As Variant 'required
Dim x As Long
Dim N As Long

For Each Shp In ActiveSheet.Shapes
If Shp.Type = msoGroup Then
x = Shp.GroupItems.Count
ReDim arrShps(1 To x)
For N = 1 To x
arrShps(N) = Shp.GroupItems(N).Name
Next
Shp.Ungroup
Set shpRng = ActiveSheet.Shapes.Range(arrShps)

For N = 1 To x
'See VBA help for "Type" Property
If shpRng(N).Type = msoChart Then
'....do something
End If
Next
Shp.Regroup 'or shpRng.Regroup
End If
Next
End Sub
Thanks for the reply Jim. Sorry I haven't responded sooner.

When I use your example an error occurs at,
Set shpRng = ActiveSheet.Shapes.Range(arrShps)

The error details are,
# 1004
The item with the specified name wasn't found.

The arrShps variable has populated with all the grouped items.
I've tried a few changes but with no success.
Any idea as to why this is happening?
 
B

BJ

BJ said:
Thanks for the reply Jim. Sorry I haven't responded sooner.

When I use your example an error occurs at,
Set shpRng = ActiveSheet.Shapes.Range(arrShps)

The error details are,
# 1004
The item with the specified name wasn't found.

The arrShps variable has populated with all the grouped items.
I've tried a few changes but with no success.
Any idea as to why this is happening?

--------------------------------------------------------------------------------------------------------------------------------
OK, I've tried various methods to access the grouped objects without
having to ungroup and regroup.

Trying to group and regroup resulted in the example code I have been
given failing. Lack of finding other
suitable code drove me to try accessing the objects without ungrouping
and regrouping. By the way, if anyone can explain why I need two object
types to access an OLE textbox property (e.g.
shpShapeItem.OLEFormat.Object.Object) maybe I'd be able to grasp shapes
a bit better.
Anyway here is the code I've written which works for my circumstances
(updating grouped charts and text
boxes), hopefully it will be of use to someone else. Any comments and
code on how I could do it better woulbe be greatly appreciated.
Thanks.


'---------- Module Level Variable -----------------
Public shpShapeItem As Excel.Shape 'Identifies the shape within the
grouped shape object.
'--------------------------------------------------

I used the following function to set the module level variable and pass
back info for procedure flow.

Function IsGrouped(SheetName As String, ObjectName As String) As
Boolean
'Identifies if the object being referenced is part of a group. Used
to
'determine method of access when updating items.

Dim shp As Excel.Shape
Dim ws As Worksheet
Dim x As Long
Dim N As Long

Set ws = Worksheets(SheetName)

'Identify if the shape object is in a group.
For Each shp In Worksheets(SheetName).Shapes
If shp.Type = msoGroup Then
x = shp.GroupItems.Count
For N = 1 To x
If shp.GroupItems(N).Name = ObjectName Then
IsGrouped = True
Set shpShapeItem = shp.GroupItems(N)
Exit Function
End If
Next N
End If
Next shp

'If shape object not found within a group then search ungrouped
shapes.
For Each shp In Worksheets(SheetName).Shapes
If shp.Name = ObjectName Then
IsGrouped = False
Set shpShapeItem = shp
Exit Function
End If
Next shp

End Function

The following procedure is one example of how I used the above
function.

Sub Set_Axis_Values(SheetName As String, ChartName As String, MaxAxis
As Double, MinAxis As Double, _
MajorUnit As Double)
' Pass the charts reference and axis details to this procedure to
amend the
' charts axis settings.

'Identify if the object is grouped or ungrouped.
If IsGrouped(SheetName, ChartName) = False Then
With
ActiveWorkbook.Worksheets(SheetName).ChartObjects(ChartName).Chart.Axes(xlValue)
.MinimumScale = MinAxis
.MaximumScale = MaxAxis
.MajorUnit = MajorUnit
End With
Else
With shpShapeItem.OLEFormat.Object.Chart.Axes(xlValue)
.MinimumScale = MinAxis
.MaximumScale = MaxAxis
.MajorUnit = MajorUnit
End With
End If

End Sub
--------------------------------------------------------------------------------------------------------------------------------
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top