VBA Excel - 如何访问工作表内框架内的控件？重构/优化

Question

使用 Excel 2010

我有一个包含三个 ActiveX 框架的工作表。这些框架中的每一个都包含两个或更多选项按钮。工作表上的重置按钮将选项按钮的值重置为“假”。

现在，我可以为每一帧使用一个单独的 For 循环来重置它们:

Private Sub CommandButtonReset_Click()

'This button resets all the OptionButtons to False (unchecked)
Dim x As Control

For Each x In Frame1.Controls
        x.Value = False
Next

For Each x In Frame2.Controls
        x.Value = False
Next

For Each x In Frame3.Controls
        x.Value = False
Next

End Sub

...但我想使用一个嵌套的 For 循环将它们全部重置，如下所示:

Dim xControl as control
Dim xFrame as Frame


For Each xFrame in (ActiveSheet.Frames? .Shapes? .OLEObjects?)
 For Each xControl in xFrame
 xControl.Value = False
 Next
Next

在网上和书籍中广泛搜索后，我找不到访问事件工作表中每个 ActiveX 框架的正确方法。

Answer 1

尝试这个:

Sub Tester()
    Dim o As OLEObject, c

    For Each o In Sheet1.OLEObjects
        'is this a Frame?
        If TypeName(o.Object) = "Frame" Then
            For Each c In o.Object.Controls
                'is this a checkbox?
                If TypeName(c) = "CheckBox" Then
                    c.Value = False
                End If
            Next
        End If
    Next o
End Sub