Accessing UserForm in WorkBook 'A' from WorkBook 'B'

[Jaafar Tribak]

Hi all and Happy new year.

I have seen this question asked many times before but surprisingly enough there is no direct method to access the Properties/Methods of a remote userform and its controls at runtime..... I needed this in a small project recently and I thought I would post here the solution I came up with as this may prove useful to others.

1- Code in the UserForm Module in Workbook A (Server workbook)

Option Explicit

Private Declare Function SetProp Lib "user32" Alias "SetPropA" _
(ByVal hwnd As Long, ByVal lpString As String, ByVal hData As Long) As Long

Private Declare Function RemoveProp Lib "user32" Alias "RemovePropA" _
(ByVal hwnd As Long, ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    
Private hwnd As Long


Private Sub UserForm_Initialize()
    hwnd = FindWindow(vbNullString, Me.Caption)
    RemoveProp hwnd, "FrmPointer"
    SetProp hwnd, "FrmPointer", ObjPtr(Me)
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    RemoveProp hwnd, "FrmPointer"
End Sub

2- Code in Worknbook B (Client workbook)

Option Explicit

Private Declare Function GetProp Lib "user32" Alias "GetPropA" _
(ByVal hwnd As Long, ByVal lpString As String) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" (Destination As Any, Source As Any, _
    ByVal Length As Long)


Sub Test()

   [B][COLOR=#008000]'\\ Get a pointer to the remote UForm and manipulate[/COLOR][/B]
   [B][COLOR=#008000] '\\ its Properties/Methods/Controls.[/COLOR][/B]
    Dim oRemoteUFrm As Object
    
    Set oRemoteUFrm = GetForm(Frm_Name:="UserForm1")
    If Not oRemoteUFrm Is Nothing Then
        With oRemoteUFrm
            .BackColor = vbBlue
            .Width = oRemoteUFrm.Width * 1.5
            .Left = 10
        End With
    Else
        MsgBox " 'UserForm1' not loaded."
    End If
    Set oRemoteUFrm = Nothing

End Sub

[B][COLOR=#008000]'\\ Function that gets a pointer to the remote UForm.[/COLOR][/B]
Function GetForm(ByVal Frm_Name As String) As Object

    Dim hwnd As Long, lPtr As Long
    Dim oTempFrm As Object
    
    hwnd = FindWindow(vbNullString, Frm_Name)
    If hwnd Then
        lPtr = GetProp(hwnd, "FrmPointer")
        CopyMemory oTempFrm, lPtr, 4
        Set GetForm = oTempFrm
        CopyMemory oTempFrm, 0&, 4
        Set oTempFrm = Nothing
    End If
 
End Function

This also works for more than one loaded userform.

 

[Jaafar Tribak]

In this second post, we will go one step further and show how to access a userform loaded in a second instance of excel.

Upon initialization, the form registers itself in the ROT(Running Object Table) so it can be accessed by other applications via the familiar VBA GetObject Function.

1- Code in the UserForm Module in Workbook A (Server Application)

Option Explicit

Private Type GUID
    Data1 As Long
    Data2 As Integer
    Data3 As Integer
    Data4(7) As Byte
End Type

Private Declare Function CLSIDFromProgID Lib "ole32.dll" _
(ByVal ProgID As Long, rclsid As GUID) As Long

Private Declare Function CoDisconnectObject Lib "ole32.dll" _
(ByVal pUnk As IUnknown, pvReserved As Long) As Long

Private Declare Function RegisterActiveObject Lib "oleaut32.dll" _
(ByVal pUnk As IUnknown, rclsid As GUID, _
ByVal dwFlags As Long, pdwRegister As Long) As Long

Private Declare Function RevokeActiveObject Lib "oleaut32.dll" _
(ByVal dwRegister As Long, ByVal pvReserved As Long) As Long

Private Const ACTIVEOBJECT_WEAK = 1
Private OLEInstance As Long

Private Sub UserForm_Initialize()
    Call AddToROT(Me, "Forms.Form.1")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Call RemoveFromROT(Me, OLEInstance)
End Sub

Private Sub AddToROT(ByRef TargetObject As Object, ByRef ProgID As String)
    Dim tGuid As GUID
    If Not Nothing Is TargetObject Then
        If CLSIDFromProgID(StrPtr(ProgID), tGuid) = 0 Then
            RegisterActiveObject TargetObject, tGuid, ACTIVEOBJECT_WEAK, OLEInstance
        End If
    End If
End Sub

Private Sub RemoveFromROT(ByRef TargetObject As Object, ByVal Instance As Long)
    If Instance Then
        RevokeActiveObject Instance, 0
    End If
        CoDisconnectObject TargetObject, 0
End Sub

2- Code in Worknbook B (Client Application)

Sub Test()

    Dim oRemoteUfrm As Object
    
    On Error GoTo Err_Handler
    [B][COLOR=#008000]'\\ Get a pointer to the remote Form.[/COLOR][/B]
    Set oRemoteUfrm = GetObject(, "Forms.Form.1")
    If Not oRemoteUfrm Is Nothing Then
        [B][COLOR=#008000]'\\ Change some of its Properties.[/COLOR][/B]
        With oRemoteUfrm
            .BackColor = vbBlue
            .Left = 0
            .Top = 0
            .Width = 500
        End With
    End If
    Exit Sub
Err_Handler:
    MsgBox "The remote Userform is not loaded or not registerd in the ROT."

End Sub

One limitation to this inter-process form communication is that it only works with one UserForm at a time.