循环遍历分组框中的每个控件

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(内的所有 FormControl 名称。

您可以添加新的 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