循环访问分组框中的每个控件

Question

我在excel表格中有一个对象列表，每个对象都有一个复选框和4个代表该对象的组框中的下拉列表。

我可以使用VBA遍历工作表中的每个分组框，但如何遍历分组框中的每个控件呢？

Dim oGroupBox As GroupBox
Dim cntrl As Control
For Each oGroupBox In Worksheets("Grapher").GroupBoxes
    For Each cntrl In oGroupBox.Controls
        Debug.Print (cntrl.Name)
    Next cntrl
Next oGroupBox

Answer 1

下面假设您没有对FormControl形状进行分组。它将列出TopLeftCell位于GroupBox范围(从TopLeftCell到BottomRightCell)内的所有窗体控件的名称。

您可以添加新的Sub代码，以便对所使用的每种类型的表单控件执行不同的操作。

Option Explicit

Sub ListControlsInGroupBoxes()
    Dim oGroupBox As GroupBox
    For Each oGroupBox In ThisWorkbook.ActiveSheet.GroupBoxes
        ListObjectsInGroupBox oGroupBox
    Next oGroupBox
End Sub

Private Sub ListObjectsInGroupBox(GBox As GroupBox)
    Dim oBoxRange As Range, oShp As Shape
    Set oBoxRange = Range(GBox.TopLeftCell, GBox.BottomRightCell)
    Debug.Print String(50, "-")
    Debug.Print "Group Box """ & GBox.Name & """ has range " & oBoxRange.Address
    For Each oShp In GBox.Parent.Shapes
        ' Deal only with FormControls
        If oShp.Type = msoFormControl Then
            ' Display FormControl's name if inside the GroupBox range
            If Not Intersect(oShp.TopLeftCell, oBoxRange) Is Nothing Then
                ' Obmit itself
                If Not oShp Is GBox Then
                    Debug.Print """" & oShp.Name & """"
                End If
            End If
        End If
    Next oShp
    Set oBoxRange = Nothing
End Sub