Show AnyForm

[levanduyet]

Trong VBA khi muốn hiện một Form chúng ta phải dùng phương thức Show như sau:

UserForm.Show

Có một cách mà trang http://www.cpearson.com/excel/ShowAnyForm.htm giới thiệu là một ShowAnyForm. Tôi xin giới thiệu đoạn code sau:

 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

Và sau đây là đoạn code để thử thủ tục trên:

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

Một khi bạn có thể Access tới một form thông qua biến chuổi thì bạn có thể dùng hàm CallByName để thiết lập các giá trị cho các control trên form đó

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

Và sau đây là đoạn code ví dụ để thiết lập các giá trị của các control trên form như đã nói ở trên

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

Các bạn có thể đọc lời giải thích theo đường link tôi đã đưa ra ở trên.
Chúc bạn thành công.

Lê Văn Duyệt