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
--------------------------------------------------------------------------------------------------------------------------------