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
|
||