How do I load every UserForm without having to call .Show individually?

Question

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?

Daniel Daniel · Accepted Answer · 2012-12-20T05:00:42

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.

How do I load every UserForm without having to call .Show individually?

2 Answers