Search code examples
excelexcel-formulaexcel-2010excel-2007vba

VBA - For Each, OptionButtons and Groups


I'd like to unactivate my option buttons but since I've grouped them with altogether my code won't work anymore.

So far I have this :

Sub Clean_sheet()
Dim Ws as Worksheet
Dim optBtn As OptionButton
Dim m As Byte

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Set Ws = ThisWorkbook.Sheets("Externe")

    For Each optBtn In Ws.OptionButtons
        optBtn.Value = -4146
    Next

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

it just skips the part "For each optBtn in...". I don't use activexControls.

My groups look like this :

enter image description here

enter image description here

I've already been helped but since I grouped my forms it does not work anymore :(. Thanks in advance for your help


Solution

  • The following code will deselect all options in group "GPE_M1":

    Dim Group_Frame As Shape
    Dim Group_Item As Shape
    
        Set Group_Frame = ws.Shapes("GPE_M1")  'top frame 
    
        For Each Group_Item In Group_Frame.GroupItems
            If Group_Item.FormControlType = xlOptionButton Then 
               Group_Item.ControlFormat.Value = -4146
            End if
        Next Group_Item