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

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


"Claude" wrote in message
...
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

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


"Claude" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

"Claude" wrote:

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

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

However, it appears that you cannot access the Value property of a shape
group member. Can anyone do that?
--
Jim Cone
San Francisco, USA

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" wrote in message
...
However, it appears that you cannot access the Value property of a shape
group member. Can anyone do that?
--
Jim Cone
San Francisco, USA

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" schrieb:

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