Userform in excel sheet-Can it be made an add in

Question

I have a userform that is used to generate reports. In case i have to share the userform with someone i share the entire excel sheet. Is it possible to make the existing user form as an Add-in.so that,once installed it can be accessible through any excel sheet that is opened and not just that particular excel sheet.

Thanks in advance.

Answer 1

Yes, you can, but, you need to do a bit of preparation.

Ensure your addin has a Project name that differs from the name of your workbook. For example, if your user's workbook's Project is called VBAProject, then your add-in's project name must be (and should be named something more appropriate anyway) as something like MyAddin.

So, you have:

• Book1.xlsm (Project name = VBAProject), and
• MyAddin.xlam (Project name MyAddin)

Steps:

1. Within Book1/VBAProject, add a reference (Tools..References) to MyAddin.
2. Within MyAddin, create your UserForm (MyUserForm)

3. For early-binding, we need to make the form instancing PublicNotCreatable, but the VBE UI doesn't offer that property for forms, so we need to export the form to a file folder, then edit the MyUserForm.frm file, changing the Attribute VB_Exposed attribute to True (by default it's False). That is, in a text editor, edit the exported file named MyUserForm.frm and adjust the existing line as follows:

   Attribute VB_Exposed = True
   

4. Save the file changes, (delete the original form in MyAddin) and then Import the MyUserForm.frm into the project. The new user form will have PublicNotCreatable instancing.

5. Add a public factory function to MyAddin, that will create a new instance of the form, and return it to any VBA that calls it:

   Public Function GetUserForm() As MyUserForm
     Set GetUserForm = New MyUserForm
   End Function
   

6. In Book1.xlsm, you can now write code like the following, along with full early-binding support.

   Public Sub test()
   
     Dim frm As MyAddin.MyUserForm
     Set frm = MyAddin.GetUserForm()
     frm.Show        
   End Sub