How to open user form in another workbook automatically using VBA. Watch the video below:
We might have created a useful form to enter data in a workbook earlier. Now we can use the same user-form by calling it from a new workbook. That saves time! Below is the complete code to automate the process of opening a user-form in another workbook:
We need to first create a procedure in the workbook that contains the form to display the form:
Sub OpenUF()
UserForm1.Show
End Sub
Next we change the project name of that VBA project. In VBA, click on Tools menu, choose VBProject Properties and change the name to, let’s say, ThisProhHasUF. Save the workbook as a macro-enabled ‘.xlsm’ file.
Next open the workbook that needs to display the form.
Select the Tools menu
Choose References
Select ThisProhHasUF from the list and check the check-box.
We can now call OpenUF from the workbook that needs to call open the form with the following VBA code:
Sub OpenUFFromOtherWbk()
Application.Run “‘wbkWithUF.xlsm’!OpenUF”
End Sub
We can also use the Application.Run method to open a userform in another file using the following code when both the Excel files are open:
Sub test()
Application.Run “‘wbkwithUF.xlsm’!OpenUF”
End Sub
Further Reading