Open Userform by name

Question

I would like to open Userforms using it´s Name without having to hardcode a Select Case-statement for each Userform.

i tried

VBA.UserForms(Integer).Show and
VBA.UserForms(String).Show

first threw error "out of range". second threw error "type mismatch"

then i tried a for each loop:

Sub Open_UserForm(ByVal UserFormName As String)
Dim Usf As UserForm
For Each Usf in VBA.UserForms
    If Usf.Name = UserFormName Then
        Usf.Show
    End If
Next Usf
End Sub

I tried this with Usf as Object too. In both cases it skipped the For Each at the first Iteration.

Then i tried it with VBProject and VBComponent:

Workbooks(WorkbookName).VBProject.VBComponents(UserFormName).Show

I tried all these Methods with Modeless too.

Answer 1

Show Userform By Name

• Based on Chip Pearson's article suggested by T.M. in your comments.
• To test the difference between when the form is loaded (hidden) and when it is not, add a button with the code Me.Hide to hide the form and set the optional ShowMessages argument to True.

Sub ShowUserForm( _
        UserFormName As String, _
        Optional ShowMessages As Variant = False)
    
    Dim obj As Object
    
    For Each obj In VBA.UserForms
        If StrComp(obj.Name, UserFormName, vbTextCompare) = 0 Then
            If ShowMessages Then MsgBox "Was already loaded.", vbInformation
            obj.Show
            Exit Sub
        End If
    Next obj
    
    On Error Resume Next
        Set obj = VBA.UserForms.Add(UserFormName)
    On Error GoTo 0

    If obj Is Nothing Then
        MsgBox "User form """ & UserFormName & """ not found!", vbExclamation
        Exit Sub
    End If
    
    If ShowMessages Then MsgBox "Was NOT loaded.", vbInformation
    obj.Show
    
End Sub