Re-setting all option buttons at once

Question

I have written a code for re-setting all option button on one click but its giving an error, "object doesnt support the property or matter".

Sub Add_New_Record()
Dim i As Integer
For i = 1 To 30
    With Sheets("Form")
        '-- unlock the worksheet
        .Unprotect
        .OptionButton(i).Value = False

        '-- lock the worksheet
        '.Protect

        .Activate
        .Range("Q12").Select
     End With
     Next i
End Sub

Can anyone please suggest me how to fix the code and make the value of all option buttons "false" at one.

I know how to do it individually like:

Sub Add_New_Record()
    With Sheets("Form")
        '-- unlock the worksheet
        .Unprotect
        .OptionButton1.Value = False

        '-- lock the worksheet
        '.Protect
        .Activate
        .Range("Q12").Select
     End With
End Sub

but since I have too many buttons, the code will get really long and inefficient.

Thanks for your help and time.

Answer 1

Loop through all the OLEObjects on a particular sheet and if it is an optionbutton then set it to false.

For i = 1 To ActiveSheet.OLEObjects.Count
    If TypeName(ActiveSheet.OLEObjects(i).Object) = "OptionButton" Then
        ActiveSheet.OLEObjects(i).Object = False
    End If
Next i

Embedding this snippet in your code:

Sub Add_New_Record()
    With Sheets(1)
        .Unprotect
        For i = 1 To .OLEObjects.Count
            If TypeName(.OLEObjects(i).Object) = "OptionButton" Then
                .OLEObjects(i).Object = False
            End If
        Next i
        .Protect
        .Range("Q12").Select
    End With
End Sub

Read more about OLEObjects here

Answer 2

First, the With statement should be before the For loop. And it should be .OptionButtons. Try this one.

Sub Add_New_Record()
    Dim i As Integer

    With Sheets("Form")
        .Unprotect
        For i = 1 To 30
            .OptionButtons(i).Value = False
        Next i
        .Protect
    End With
End Sub