How to use a UserForm in any excel file, not only in the one in which the UserForm locates

[namy77]

So, I have a simple UserForm to query data from a ComboBox save in one of the tabs of an Excel file that I leave open at work:

Please Login or Register  to view this content.

The source of data looks something like this:

g1ZMbgB.png

With names assigned so I can query data using 2 variables:

qFPrX1m.png

Everything works great and all, but I would need to run this macro from this worksheet, in order to get this dialogue:

0z5HPyo.png

Is there anyway to do it so that I can run this macro (by adding a button in the ribbon, for example) from any random excel spreadsheet so I can query for this data and get the form above, as opposed to something like this:

tcSQuIB.png

Thanks for your help!

[Neil_]

Yes,
you can save the userform in your Personal Macro Workbook. To create one, record a macro and choose 'Personal Macro Workbook' from the drop down list. You can then copy your userform there (Ctrl>Drag it) and hit the save button in the VB Editor.

Capture.PNG

Alternatively, you can save the workbook with the UserForm as an Add-In

Capture 2.PNG

As long as you have the add-in enabled, in File>Options>Add Ins> Manage Excel Add Ins, then the user form will be available

[namy77]

Hmm, I tried save as add in, then activate it in the add in manage option (then add macro into my menu using the customize ribbon function) but the form is still not populate correctly.

I will try with the personal work book thing. Would I need to copy the worksheet that contains my list as well? Thanks!

[Neil_]

No, just the Userform. You need a method of launching it. If you create a macro with Userform1.Show as the line of code (either in the Personal Macro Workbook in a new module, or the same in the add-in), you can then assign a button to it by customizing your Quick Access toolbar and adding it.
Capture3.PNG