VBA-项目中所有控件的列表

debugcn 发表于 Dev

索非亚

我收到以下代码的424错误“ object required”。下面的代码目标是打印VBA项目中所有控件的列表。为什么会出现此424错误？

Public Sub List_Controls_All_Userforms()

    '<<<< Ojective:  I want to create a list of all controls in my project, including Userform.Names.


    Dim individualUserform As Object
    Dim controlItem As Control
    Dim rowNumber As Long
    Dim cell As Range

    For Each cell In Sheets("TabNames").Range("H1:H50")   '<<<<< 50 userform names.
        Set individualUserform = cell.Value               '<<<<< Error 424 object required. <<< Calling each form by name like this is wrong??
        For Each controlItem In individualUserform.Controls
          rowNumber = rowNumber + 1
          Sheets("TabControls").Cells(rowNumber, 1).Value = individualUserform.Name & "." & controlItem.Name
        Next controlItem
    Next cell
End Sub

再次在我的原始文本中添加了屏幕快照，而不是在Rajesh的回复中添加了屏幕快照（由于某些原因屏幕快照没有上传到那里）。

病毒

不，我担心那是不正确的。但是，您可以使用/循环VBComponents工作簿中的集合VBProject：

如果要遍历指定的范围，因为您写下了用户窗体的名称，请尝试：

Public Sub List_Controls_All_Userforms()

Dim controlItem As Control
Dim rowNumber As Long
Dim cell As Range

For Each cell In Sheets("TabNames").Range("H1:H50")
    For Each controlItem In ThisWorkbook.VBProject.VBComponents(cell).Designer.Controls
        rowNumber = rowNumber + 1
        Sheets("TabControls").Cells(rowNumber, 1).Value = cell & "." & controlItem.Name
    Next controlItem
Next cell

End Sub

如果您不想在一个范围内写下所有用户窗体名称并对其进行循环，则也可以尝试循环该VBComponents集合并检查它们的.Type（3 = UserForm），如下所示：

Public Sub List_Controls_All_Userforms()

Dim controlItem As Control
Dim rowNumber As Long
Dim obj as object

For Each obj In ThisWorkbook.VBProject.VBComponents
    If obj.Type = 3 Then
        For Each controlItem In ThisWorkbook.VBProject.VBComponents(obj.Name).Designer.Controls
            rowNumber = rowNumber + 1
            Sheets("TabControls").Cells(rowNumber, 1).Value = obj.Name & "." & controlItem.Name
        Next controlItem
    End If
Next obj

End Sub