[IncludeBorders/top.htm]

Show Any Form

  In VBA, the usual way to show a form is to simply use the Show method.  However, this method requires that you hard-code the UserForm's name in your code. For example,
    UserForm1.Show

But there may be circumstances in which you do not know until run-time which UserForm you need to display. In this case, you can use the often overlooked VBA.UserForms object. This object works much like a Collection object and holds references to all the UserForms that are currently loaded, and possibly shown.  The Add method of the VBA.UserForms object allows you to load a UserForm by its name as a string. Thus, you can determine at run-time which form to load, passing a string variable to the Add method of VBA.UserForms.

The procedure ShowAnyForm, shown below, accepts as input the name of the form to show and the modal method -- vbModeless or vbModal -- to use to show the form. If the form is already loaded, it is not unload and reloaded. It is simply made visible using Show. If the UserForm is not loaded, and thus is not included in the VBA.UserForms object, it is loaded using the Add method and then made visible with Show. In the procedure shown below, the form is assumed to have a Label control named Label1. Its Caption is set to indicate whether the form was already loaded or whether it was loaded by ShowAnyForm. Of course, you will want to remove those lines of code in a real world  application.

    Sub ShowAnyForm(FormName As String, Optional Modal As FormShowConstants = vbModal)
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ShowAnyForm
    ' This procedure will show the UserForm named in FormName, 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.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        Dim Obj As Object
        ''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' Loop through the VBA.UserForm object (works like
        ' a collection), to see if the form named by
        ' FormName is already loaded. If so, just call
        ' Show and exit the procedure. If it is not loaded,
        ' add it to the VBA.UserForms object and then
        ' show it.
        ''''''''''''''''''''''''''''''''''''''''''''''''''''
        For Each Obj In VBA.UserForms
            If StrComp(Obj.Name, FormName, vbTextCompare) = 0 Then
                ''''''''''''''''''''''''''''''''''''
                ' START DEBUGGING/ILLUSTRATION ONLY
                ''''''''''''''''''''''''''''''''''''
                Obj.Label1.Caption = "Form Already Loaded"
                ''''''''''''''''''''''''''''''''''''
                ' END DEBUGGING/ILLUSTRATION ONLY
                ''''''''''''''''''''''''''''''''''''
                Obj.Show Modal
                Exit Sub
            End If
        Next Obj
        
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        ' If we make it here, the form named by FormName was
        ' no loaded, and thus not found in VBA.UserForms.
        ' Call the Add method of VBA.UserForms to load the
        ' form and then call Show to show the form.
        ''''''''''''''''''''''''''''''''''''''''''''''''''''''''
        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
                ''''''''''''''''''''''''''''''''''''
                ' START DEBUGGING/ILLUSTRATION ONLY
                ''''''''''''''''''''''''''''''''''''
                Obj.Label1.Caption = "Form Loaded By ShowAnyForm"
                ''''''''''''''''''''''''''''''''''''
                ' END DEBUGGING/ILLUSTRATION ONLY
                ''''''''''''''''''''''''''''''''''''
            Obj.Show Modal
        End With
    End Sub

Since ShowAnyForm takes a string argument, you can set the form to display at run time, as shown in the example below:

    Sub AAATest()
        Dim FormName As String
        Dim Something As Long
        
        Something = 2 ' or whatever
        ''''''''''''''''''''''''''''''''''
        ' Determine which form to display.
        ''''''''''''''''''''''''''''''''''
        Select Case Something
            Case 1
                FormName = "UserForm1"
            Case 2
                FormName = "UserForm2"
            Case Else
                FormName = "UserForm3"
        End Select
        
        ''''''''''''''''''''''''''''''''''''
        ' Show the form.
        ''''''''''''''''''''''''''''''''''''
        ShowAnyForm FormName:=FormName, Modal:=vbModal
    End Sub

 

Once you can access a form with a string variable, you can use the CallByName function to retrieve or set the value of a control on the form. Because CallByName can use string variables to specify the control and property, you can determine at run time which control and which property of that control you want to set or retrieve. The ControlValueByName function shown below will do this:

    Function ControlValueByName(FormName As String, ControlName As String, ProcName As String, _
        CallType As VbCallType, Optional Value As Variant) As Variant
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ControlValueByName
    ' This procedure allows you to set or get the value of a control on a form or
    ' execute a method of the form. The form named in FormName will be loaded if
    ' necessary.
    ' FormName is the name of the UserForm.
    ' ControlName is the name of the control (e.g., "Label1").
    ' ProcName is the name of the Property or procedure.
    ' CallType indicates whether to Let a property or Get a property. Must be 
    '          one of VbGet, VbLet, or VbMethod.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim Res As Variant
    Dim Obj As Object
    Dim Ctrl As MSForms.Control
    For Each Obj In VBA.UserForms
        If StrComp(FormName, Obj.Name, vbTextCompare) = 0 Then
            Exit For
        End If
    Next Obj
    If Obj Is Nothing Then
        Err.Clear
        Set Obj = VBA.UserForms.Add(FormName)
        If Err.Number <> 0 Then
            MsgBox "Error Calling Form: " & FormName
            ControlValueByName = Null
            Exit Function
        End If
    End If
    
    Err.Clear
    Set Ctrl = Obj.Controls(ControlName)
    If Err.Number <> 0 Then
        MsgBox "Error On Control: '" & ControlName & "' of UserForm: '" & FormName & "'."
        ControlValueByName = Null
    End If
    
    Select Case True
        Case CallType = VbGet
            Res = CallByName(Ctrl, ProcName, VbGet)
            ControlValueByName = Res
            Exit Function
        Case CallType = VbLet
            CallByName Ctrl, ProcName, VbLet, Value
        Case CallType = VbMethod
            Res = CallByName(Obj, ProcName, VbMethod)
            ControlValueByName = Res
    End Select
    
    End Function
    

You can call ControlValueByName as shown in the example code below. The code shown below will set the value of the Caption of Label2 of form UserForm2.

Sub SetPropertyAtRunTime()

    Dim FormName As String
    Dim ControlName As String
    Dim ProcName As String
    Dim CallType As VbCallType
    Dim Res As Variant
    Dim Value As Variant
    
    FormName = "UserForm1"
    ControlName = "Label2"
    ProcName = "Caption"
    CallType = VbLet
    Value = "New Caption Text"
    
    Res = ControlValueByName(FormName:=FormName, ControlName:=ControlName, _
        ProcName:=ProcName, CallType:=CallType, Value:=Value)
    ShowAnyForm FormName
    
End Sub

 

 

 
     
     
[IncludeBorders/bottom.htm]