Selecting only group boxes, not the option buttons inside

[deepfriedcheese]

I have a spreadsheet originally created with Excel 2000 or earlier, but now running in Excel 2010 on a Windows 7 machine. One of the sheets in this workbook has several dozen group boxes created from the Form Controls menu. Inside each group box are exactly two option buttons also created from the Form Controls menu.

This spreadsheet has passed through many hands and is a mess to look at. The group boxes and option buttons are in two columns, but they are not even close to being in a straight line. Because I have several spreadsheets of this nature to maintain for a large group of users, I'd like them to look more presentable without going blind manually moving boxes and buttons around.

My question: Using a macro, is there a way to select only the group boxes in order to resize or move them? I assume if it can be done for the group boxes, the option buttons should work the same. I can loop through all of the shapes on the sheet and set height and position relative to the top of the cell without differentiating between the types of controls, but to set the width I need to know whether each shape is a group box or an option button as otherwise the option buttons would overlap and extend beyond the right edge of the group box.

This is the code I have so far to clean these spreadsheets up.

Sub DynamicRepair()
'Look through the selected range.  Set group boxes and radio buttons to preset positions.
Dim myshape As Shape
 
For Each cell In Selection
    For Each myshape In ActiveSheet.Shapes
            If Not Intersect(myshape.TopLeftCell, cell.EntireRow) Is Nothing Then
                'Set a fixed height
                myshape.Height = 0.75
                'Center vertically in cell
                myshape.Top = cell.Top + ((cell.Height / 2) - 9.75)
 
            End If
    Next
Next
 
End Sub

I realize I will probably need to remove the EntireRow criteria from the selection when I figure out how to set the width of the types individually.

 

[mikerickson]

This code will sort out what kind of shape one is dealing with.

Dim oneShape As Shape

For Each oneShape In ActiveSheet.Shapes
    With oneShape
        If .Type = msoFormControl Then
            Select Case .FormControlType
                Case xlGroupBox
                    MsgBox .Name & " is a group box"
                Case xlOptionButton
                    MsgBox .Name & " is an option button"
            End Select
        Else
            MsgBox .Name & " is not a form control"
        End If
    End With
Next oneShape