Selecting only group boxes, not the option buttons inside

deepfriedcheese

New Member
Joined
Aug 6, 2010
Messages
21
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.

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,776
This code will sort out what kind of shape one is dealing with.

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

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top