Add userform to a different workbook at runtime

Question

I have an addin and a workbook open. The addin is a .xlam file and in the workbook I've added a reference to it. The addin is password protected.

It is possible to run public methods of the addin from my workbook. However one method in the addin makes use of VBA.UserForms.Add to open a userform that was created at runtime like this

Let's say the workbook which holds a reference to myAddin has this:

Private Sub callAddin()
    myAddin.ShowForm ThisWorkbook
End Sub

Ordinarily, the code in my addin looks like this:

Public Sub ShowForm(CallerWorkbook As Workbook)
    Const vbext_ct_MSForm As Long = 3

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False

    'Add to ThisWorkbook, not supplied workbook or VBE will crash - ignore CallerWorkbook
    Dim myForm As Object
    Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    'Create the User Form
    With myForm
        .Properties("Caption") = "Select"
        .Properties("Width") = 300
        .Properties("Height") = 270
    End With

    'Show the form
    Dim finalForm As Object
    Set finalForm = VBA.UserForms.Add(myForm.Name)
    finalForm.Show

    'Remove form
    ThisWorkbook.VBProject.VBComponents.Remove myForm

End Sub

Which works fine. However when my addin is password protected, trying to add a temporary userform to it is not allowed. No problem, I just add the temporary userform to the workbook that called the code instead, as this will not be password protected

Sub ShowForm(CallerWorkbook As Workbook)
    Const vbext_ct_MSForm As Long = 3

    'This is to stop screen flashing while creating form
    Application.VBE.MainWindow.Visible = False

    'Add to CallerWorkbook instead
    Dim myForm As Object
    Set myForm = CallerWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

    'Create the User Form
    With myForm
        .Properties("Caption") = "Select"
        .Properties("Width") = 300
        .Properties("Height") = 270
    End With

    'Show the form
    Dim finalForm As Object
    'Now myForm cannot be found and added
    Set finalForm = VBA.UserForms.Add(myForm.Name)
    finalForm.Show

    'Remove form
    CallerWorkbook.VBProject.VBComponents.Remove myForm

End Sub

However VBA can't seem to see where myForm.Name points to now, so the Add method fails with "Run time error 424: Object required"

Is there any way to display a form created at runtime in another workbook?

Answer 1

The problem that you're encountering is that UserForms are Privately instanced by default. That means that a project cannot refer to a UserForm in another project, and if you can't refer to the form, you can't call it's Show method.

Your Set myForm = CallerWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) statement returns a VbComponent, not a UserForm, so that's why you can't then use VBA.UserForms.Add(myForm.Name)

There are 2 ways around this:

1 - Create a PublicNotCreatable template UserForm in your add-in

A UserForm is like a class, so it can have its Instancing property set, just like a class. However, the VBE doesn't expose the Instancing property in the Properties Window for UserForms, so to set the instancing, you need to export the form, and then edit the Attribute VB_Exposed attribute in the FRM file in a text editor, before importing the form again. Here are the steps:

• Create a UserForm named TemplateForm in your add-in project
• Remove TemplateForm and choose to Export the form before removing it
• Open the TemplateForm.frm file in a text editor
• Edit the line Attribute VB_Exposed = False so that is reads Attribute VB_Exposed = True
• Save the changes to TemplateForm.frm
• Import TemplateForm.frm into your add-in

• Add a public function that returns a new instance of TemplateForm to your add-in. I've made this function accept a workbook reference so that the add-in can configure any workbook specific properties on the form:

  Public Function GetTemplateForm(CallerWorkbook As Workbook) As TemplateForm
    Dim frm As TemplateForm
    Set frm = New TemplateForm
    'Set early-bound properties with intellisense
    frm.Caption = "Select"
    frm.Width = 300
    frm.Height = 270
  
    'Configure CallerWorkbook specific form properties here
    '...
    Set GetTemplateForm = frm
  End Function
  

• In your user's workbook, you can then show an instance of the TemplateForm, without ever having to dynamically add a form, or deal with screen-flickering, or hard-to-debug code:

  Sub ShowAddinForm()
      With MyAddin.GetTemplateForm(ThisWorkbook)
          'Do more workbook specific propery setting here...
          '...
          .Show
      End With
  End Sub
  

** Note - The Rubberduck VBA add-in will soon have the ability to add a PublicNotCreatable UserForm.

2 - Have the add-in create the UserForm component, but have the user's workbook manage it

This approach isn't nearly as elegant. There's a lot more code for the user to manage, and there's screen flickering, and hard to debug code. Here are the steps:

• Add this code to the add-in:

  Public Function GetTempFormName(CallerWorkbook As Workbook) As String
      Const vbext_ct_MSForm As Long = 3
  
      'This is to stop screen flashing while creating form
      Application.VBE.MainWindow.Visible = False
  
      'Add to CallerWorkbook instead
      With CallerWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
          .Properties("Caption") = "Select"
          .Properties("Width") = 300
          .Properties("Height") = 270
          GetTempFormName = .Name
      End With
  End Function
  
  Public Sub RemoveTempForm(CallerWorkbook As Workbook, FormName As String)
      With CallerWorkbook.VBProject.VBComponents
          Dim comp As Object
          Set comp = .Item(FormName)
          .Remove .Item(FormName)
      End With
  End Sub
  

• Then, in the user's workbook, add this code:

  Sub GetAddinToCreateForm()
      Dim FormName As String
      FormName = MyAddin.GetTempFormName(ThisWorkbook)
      With VBA.UserForms.Add(FormName)
          .Show
      End With
      MyAddin.RemoveTempForm ThisWorkbook, FormName
  End Sub