' Description : This procedure will show/loads the UserForm named, either modally or modelessly, as indicated by the value of Modal. If a form is already loaded, it is reshown without unloading/reloading the form.
'***********************************************************************************
Call ShowAnyForm FormName:="UserForm1", sCaptionText:="Kanagal", Modal:=vbModal
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ShowAnyForm
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ShowAnyForm(ByVal FormName$, Optional ByVal sCaptionText$ = "Form Loaded By ShowAnyForm", _
Optional Modal As FormShowConstants = vbModal)
Dim Obj As Object
For Each Obj In VBA.UserForms
If StrComp(Obj.Name, FormName, vbTextCompare) = 0 Then
Obj.Label1.Caption = "Form Already Loaded"
Obj.Show Modal
Exit Sub
End If
Next Obj
With VBA.UserForms
On Error Resume Next
Err.Clear
Set Obj = .Add(FormName)
If Err.Number <> 0 Then
MsgBox "Err: " & CStr(Err.Number) & " " & Err.Description
Exit Sub
End If
Obj.Caption = sCaptionText
Obj.Show Modal
End With
End Sub
[Via]