check whether shape is a group

[Claude]

Hi all

I'm trying to run the following macro to reset the entries in option buttons
and check boxes.

Sub ResetSelection()

For Each shp In ActiveSheet.Shapes
For Each grp In shp.GroupItems
If TypeOf grp.DrawingObject.Object Is MSforms.OptionButton Or TypeOf
grp.DrawingObject.Object Is MSforms.CheckBox Then
grp.DrawingObject.Object.Value = False
End If
Next grp
Next shp
End Sub

This works fine for the shapes that are grouped objects, however
shp.groupitems gives an error if shp is not a group. What is the correct
syntax to perform a check on shp, e.g. something like: if shp.type =
groupedobject then...

Thanks in advance

 

[Bernie Deitrick]

Claude,

Handle the error:

Sub ResetSelectionV2()
For Each shp In ActiveSheet.Shapes
On Error GoTo NotGrouped
For Each grp In shp.GroupItems
If TypeOf grp.DrawingObject.Object Is MSforms.OptionButton Or _
TypeOf grp.DrawingObject.Object Is MSforms.CheckBox Then
grp.DrawingObject.Object.Value = False
End If
Next grp
NotGrouped:
Resume noErr:
noErr:
Next shp
End Sub


HTH,
Bernie
MS Excel MVP

 

[Peter T]

When processing 'GroupObjects' it may be worth doing in recursive fashion as
groups can contain a tree like structure of sub groups, eg

Sub test()
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
Resetter shp
Next
End Sub

Sub Resetter(shp As Shape)
Dim gi As Shape

If shp.Type = msoGroup Then
For Each gi In shp.GroupItems
Resetter gi
Next
ElseIf shp.Type = msoOLEControlObject Then
With shp.DrawingObject
If TypeOf .Object Is msforms.OptionButton Or _
TypeOf .Object Is msforms.CheckBox Then
.Object.Value = False
End If
End With
End If

End Sub

Regards,
Peter T

 

[Tom Ogilvy]

This approach worked for me:

Sub ResetSelection()
Dim grps As Object, shp As Shape
For Each shp In ActiveSheet.Shapes
On Error Resume Next
Set grps = shp.GroupItems
On Error GoTo 0
If grps Is Nothing Then
Debug.Print shp.Name
Else
For Each grp In grps
Debug.Print grp.Name
Next grp
End If
Next shp
End Sub

 

[Ken Johnson]

Hi Claude,

For grouped shapes .Type = 6. (or msoGroup)
Refer to "Type Property" in the VBA Help files then look for "Shape,
ShapeRange"

Also, selecting a shape then typing...

?Selection.ShapeRange.Type

in the Immediate Window of the VBA Editor, then pressing Enter, will
return the Shape's Type value.

Ken Johnson

 

[Jim Cone]

However, it appears that you cannot access the Value property of a shape
group member. Can anyone do that?

 

[Peter T]

Hi Jim,

From the OP's code sample it would appear he has worksheet controls and
their value properties can be changed while grouped. But as you say Forms
control Value properties cannot be written while grouped, though they can be
read.

Regards,
Peter T

 

[Jim Cone]

Hi Peter,
The OP's code won't compile ( user defined type not defined ) and I
don't know what kind of controls he is trying to use.
I have Forms toolbar controls on my test worksheet and they have to be
ungrouped in order to read their values.
That part of course is not a problem, but to regroup one needs a ShapeRange.
Regards,
Jim Cone



From the OP's code sample it would appear he has worksheet controls and
their value properties can be changed while grouped. But as you say Forms
control Value properties cannot be written while grouped, though they can be
read.

Regards,
Peter T

 

[Peter T]

The OP's code won't compile ( user defined type not defined ) and I

don't know what kind of controls he is trying to use.

I didn't try the OP's code but he used TypeOf MSForms.OptionButton and later
OLEObject.Object, both of which suggest Worksheet controls.

I have Forms toolbar controls on my test worksheet and they have to be
ungrouped in order to read their values.

I slotted the following into the "Resetter" routine I posted earlier

'If shp.Type = msoGroup Then
'code
'ElseIf shp.Type = msoOLEControlObject Then
'code
ElseIf shp.Type = msoFormControl Then
If TypeOf shp.DrawingObject Is Excel.OptionButton Or _
TypeOf shp.DrawingObject Is Excel.CheckBox Then
'or
'If TypeName(shp.DrawingObject) = "OptionButton" Or _
TypeName(shp.DrawingObject) = "CheckBox" Then

Debug.Print shp.Name, shp.DrawingObject.Value
End If
'End if

With grouped Form's controls I can read values (xlOn & xlOff), but I can't
change them.

I think to change these requires storing the original group (& sub group)
structure & names, ungroup, process, regroup riginal structiure, rename 'as
was' including any sub groups. Apart from being relatively slow each regroup
first gets a new default name and the internal object counter used for names
increments. There are other properties in GroupObjects that can only be
changed this way, in particular Font (AFAIK).

Regards,
Peter T

Hi Peter,
The OP's code won't compile ( user defined type not defined ) and I
don't know what kind of controls he is trying to use.
I have Forms toolbar controls on my test worksheet and they have to be
ungrouped in order to read their values.
That part of course is not a problem, but to regroup one needs a ShapeRange.
Regards,
Jim Cone



From the OP's code sample it would appear he has worksheet controls and
their value properties can be changed while grouped. But as you say Forms
control Value properties cannot be written while grouped, though they can be
read.

Regards,
Peter T

 

[Jim Cone]

Peter,
This seems to be working for me .
Regards
Jim Cone

'------------------------------
Sub SetButtonValues()
Dim Shp As Excel.Shape
Dim shpRng As Excel.ShapeRange
Dim arrShps() As Variant
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
If shpRng(N).FormControlType = xlCheckBox Or _
shpRng(N).FormControlType = xlOptionButton Then
MsgBox shpRng(N).Name & " Value is " & shpRng(N).ControlFormat.Value
End If
Next
shpRng.Regroup
End If
Next
End Sub
'--------------

The OP's code won't compile ( user defined type not defined ) and I
don't know what kind of controls he is trying to use.

I didn't try the OP's code but he used TypeOf MSForms.OptionButton and later
OLEObject.Object, both of which suggest Worksheet controls.

I have Forms toolbar controls on my test worksheet and they have to be
ungrouped in order to read their values.

I slotted the following into the "Resetter" routine I posted earlier

'If shp.Type = msoGroup Then
'code
'ElseIf shp.Type = msoOLEControlObject Then
'code
ElseIf shp.Type = msoFormControl Then
If TypeOf shp.DrawingObject Is Excel.OptionButton Or _
TypeOf shp.DrawingObject Is Excel.CheckBox Then
'or
'If TypeName(shp.DrawingObject) = "OptionButton" Or _
TypeName(shp.DrawingObject) = "CheckBox" Then

Debug.Print shp.Name, shp.DrawingObject.Value
End If
'End if

With grouped Form's controls I can read values (xlOn & xlOff), but I can't
change them.

I think to change these requires storing the original group (& sub group)
structure & names, ungroup, process, regroup riginal structiure, rename 'as
was' including any sub groups. Apart from being relatively slow each regroup
first gets a new default name and the internal object counter used for names
increments. There are other properties in GroupObjects that can only be
changed this way, in particular Font (AFAIK).

Regards,
Peter T

 

[Peter T]

Hi Jim,

Yes that would work providing each Group only contained Form controls, and
not some other shape type. If any possibility that's not the case would need
to add an extra check before returning .FormControlType -

If shpRng(N).Type = msoFormControl Then

'Some other' shape type might be another sub-group! If so would mean
something recursive along the lines of my first post or a Do Loop.

Regards,
Peter T

 

[Claude]

Thank you to all who contributed. I solved the problem using Bernie's reply
as a basis, but very much appreciate the other information as well.

Sub ResetSelectionV3()
For Each shp In ActiveSheet.Shapes
On Error GoTo NotGrouped
For Each grp In shp.GroupItems
If TypeOf grp.DrawingObject.Object Is MSforms.OptionButton Or _
TypeOf grp.DrawingObject.Object Is MSforms.CheckBox Then
grp.DrawingObject.Object.Value = False
End If
Next grp
NotGrouped:
Resume noErr:
noErr:
On Error Resume Next
If TypeOf shp.DrawingObject.Object Is MSforms.OptionButton Or _
TypeOf shp.DrawingObject.Object Is MSforms.CheckBox Then
shp.DrawingObject.Object.Value = False
End If
Next shp
End Sub

 

[Claude]

FYI: Your original code worked just fine for me: I tested this by groupin
subgroups of controls together with pictures etc, and it did just what it was
supposed to do.