一次重新设置所有选项按钮 [英] Re-setting all option buttons at once

问题描述

我已经编写了用于一键重置所有选项按钮的代码，但是它给出了一个错误消息，对象不支持属性或物质".

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

任何人都可以建议我如何修复代码并使所有选项按钮的值都设为"false".

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.

感谢您的帮助和时间.

推荐答案

浏览特定工作表上的所有OLEObject，如果它是一个optionbutton，则将其设置为false.

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

在此处处了解更多有关OLEObjects的信息

Read more about OLEObjects here

这篇关于一次重新设置所有选项按钮的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！