Referencing a Userform in another open workbook.

[Engineer Joe]

i'm trying to show a userform from a different open workbook, but i can't get the syntax right...

i've assigned the other workbook name to a variable: helperfile

i was hoping i could just use something like:

helperfile.userformname.show

but this gives me an error.

Anyone know the correct method???

 

[VoG]

Try

Workbooks(helperfile).userformname.Show

(untested)

 

[RoryA]

As far as I know, you would need a function in the workbook with the userform that returns an Object set to an instance of the userform. You can then use the Show method on that. If you set a reference to the other workbook, you can call the function directly; if not, you need to use Application.Run.
HTH

 

[Engineer Joe]

sorry VoGII. That doesn't work. It would if i were using a string to reference my other workbook but the variable is defined as an actual workbook object...

Working on understanding rorya's comment...

 

[Legacy 98055]

Is a bit tricky. There are several ways. Here is one.

Create a workbook named Helper.xls. Add UserForm1. Enter this code into the workbook class.

Public Property Get HelperForm() As Object
    Set HelperForm = UserForm1
End Property

Save the workbook. Leave it open.

From any other open workbook, add this code in any public module.

Sub Example()
    Dim WBHelper As Workbook, UF As Object
 
    Set WBHelper = Application.Workbooks("Helper.xls")
    Set UF = WBHelper.HelperForm
    UF.Show
End Sub

If there is already an instance of userform1 loaded in Helper.xls, it is up to you to maintain a reference and provide that to clients by way of your property procedure. If not, a new instance of the form is created. In a real world project, you would not usually provide an implicit reference as such:
Set HelperForm = UserForm1

Instead, you would maintain your reference, rather initialized or not, at the module level of the workbook class in helper.xls.

Did I confuse you even more?

 

[Legacy 98055]

I hope I did not over-simplify the example. You have to be careful when opening a userform by an implicit reference. You should always provide yourself with a reference to the userform.

Implicit:
UserForm1.Show

Explicit:
Dim Uf as UserForm1
Set Uf = New UserForm1
Uf.Show

For example, the property procedure might check for an existing instance.
In Helper.xls...

Option Explicit

Private pHelperForm As UserForm1

Public Property Get HelperForm() As Object
    If Not pHelperForm Is Nothing Then
        Set HelperForm = UserForm1
    Else
        Set HelperForm = New UserForm1
    End If
End Property

Or perhap you might add an argument to request a new instance even if one already exists...

Private pHelperForm As UserForm1

Public Property Get HelperForm(Optional NewInstanceOnly As Boolean) As Object
    If Not pHelperForm Is Nothing And Not NewInstanceOnly Then
        Set HelperForm = UserForm1
    Else
        Set HelperForm = New UserForm1
    End If
End Property

I hope I'm being clear...

 

[RoryA]

Similar to Tom's. WbkA contains userform1 and a function like this:

Function GetForm() As Userform1
    Set GetForm = Userform1
End Function

WbkB can then use:

Sub blah()
   Dim frm as Object
   set frm = Application.Run("WbkA!GetForm")
   frm.Show
End Sub