更新时间:2023-12-06 12:54:58
根据此页面: UserForm对象
UserForms集合是一个集合,其元素表示应用程序中每个
加载的UserForm。由于您的Userforms未加载,因此它们不会出现在集合中。这意味着您需要以不同的方式加载表单。为了获得每个UserForm的名称,您需要允许代码访问Visual Basic项目的权限。否则,您需要提供Userform的名称。
以下内容将打开当前VBProject中的每个UserForm。
Sub OpenAllUserForms()
Dim VBComp As Object
对于每个VBComp在Application.VBE.ActiveVBProject.VBComponents
如果VBComp.Type = 3 Then'3 = vbext_ct_MSForm
VBA.UserForms。添加(VBComp.Name)。显示
结束如果
下一个
End Sub这是因为每个UserForm在VBProject中列为VBComponent。一旦代码确定哪些组件是UserForms,它会将Userform添加到集合并显示它。如果您省略了
.Show
表单仍将运行Initialize事件,但是立即超出范围并消失。I wanted to figure out how you could load every UserForm without having to call
Userform1.Show
UserForm2.Show
etc. This was inspired by comments on this answer: Excel VBA UserForm_Initialize() in Module.I found this method suggested in a few places:
Sub OpenAllUserForms() Dim uf As UserForm For Each uf In UserForms uf.Show Next End Sub
However, no Userforms display regardless of how many are attached to the workbook. When I stepped through the code I determined that the UserForms collection is empty!
How can I load each Userform without having to explicitly show each one?
According to this page: UserForm Object
The UserForms collection is a collection whose elements represent each loaded UserForm in an application.
Since your Userforms are not loaded they do not appear in the collection. This means you'll need to load the forms a different way. In order obtain the name of each UserForm you will need to allow permission for code to access the Visual Basic Project. Otherwise, the name of the Userform will need to be provided by you.
The following will open every UserForm in the current VBProject.
Sub OpenAllUserForms() Dim VBComp As Object For Each VBComp In Application.VBE.ActiveVBProject.VBComponents If VBComp.Type = 3 Then '3 = vbext_ct_MSForm VBA.UserForms.Add(VBComp.Name).Show End If Next End Sub
This works because each UserForm is listed in the VBProject as a VBComponent. Once the code determine which components are UserForms, it adds the Userform to the collection and displays it. If you omit the
.Show
the form will still run the Initialize event, but then immediately go out of scope and vanish.