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.

"Peter T" schrieb:

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

"Jim Cone" wrote in message
...
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
'--------------

"Peter T" <peter_t@discussions
wrote in message
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