☰

編輯：

tag-icon • tag-icon microsoft-excel vba
編輯：

我在下面的代码中得到了 424 错误“需要对象”。下面代码的目的是打印 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 的回复中（由于某种原因，截图没有上传到那里）。

答案1

不，恐怕这不正确。但是，您可以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 = 用户窗体），如下所示：

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

笔记按照这回答说这需要额外的安全权限>Excel 选项>信任中心>宏设置>信任对 VBA 项目对象模型的访问。

答案2

您可以使用此 VBA 代码来修复此问题：

Sub ListControls() 
    Dim lCntr As Long 
    Dim aCtrls() As Variant 
    Dim ctlLoop As MSForms.Control 

    
    For Each ctlLoop In YourForm.Controls 
        lCntr = lCntr + 1: Redim Preserve aCtrls(1 To lCntr) 
    
        aCtrls(lCntr) = TypeName(ctlLoop)&":"&ctlLoop.Name 
    Next ctlLoop 
    
    Worksheets("Sheet1").Range("A1").Resize(UBound(aCtrls)).Value = Application.Transpose(aCtrls) 
End Sub

注意：

  • YourForm用真实的表单名称、工作表名称和单元格引用替换。
  • 此代码将所有控件添加到Sheet 1。

編輯：

如果您想列出当前项目中所有用户表单的控件，可以使用此 VBA 代码。

Sub test1()

Dim i, k As Long
Dim dForm As UserForm
Dim ctl As MSForms.Control
Dim c()
Dim fmBCMcontact    
  
    Set dForm = ThisWorkbook.VBProject.VBComponents("fmBCMcontact").Designer
  i = 0
  For Each ctl In fmBCMcontact.Controls
    ReDim Preserve c(i)
    c(i) = ctl.Name
    i = i + 1
    ReDim Preserve c(i)
    c(i) = TypeName(ctl)
    Sheets("TabResult").Cells(i, 1).Value = c(i)   
    i = i + 1
  Next
  
End Sub

相关内容