If you leave the code/form in the workbook it was created in, then that workbook needs to be open every time you run the code or use the form.
If you move everything to PERSONAL.XLSB, you can use the code/form in any workbook.
There are 3 ways to move code and forms into your PERSONAL.XLSB.
Before we go any further, let's just have a quick look at forms and the code that makes them work.
A form has its own VBA code, separate to what is in code modules. If you double click a form module you see the form design. To see the code, you can right click on the module and choose View Code, or right click on the form and choose View Code.
Double clicking (with left mouse button) will also open the underlying code in a form, but the difference here is that this will create a sub for the _Click event for whatever you double clicked, if that sub does not already exist.
If it does exist, because you or someone else already wrote it, then double clicking will open the form code and show you the code for that sub.
Confused? Let’s look at an example. My form looks like this
If I double click the Delete button when I am looking at the form in the VBA editor, then the form code will be displayed, and my cursor will be in the Delete_Click sub.
Manually Create Modules, then Copy & Paste
If you want to only copy/move portions of code, then this is probably the way to do it. If you want to move/import whole modules, then use the drag and drop method below.
If you are working with forms, I’d recommend that you use the Export/Import or Drag/Drop method.
Right click in the project and choose Insert then either UserForm, Module or Class Module, and a new module is created. It will have a default name e.g. Module1. You can change this in the Properties window of the VBA editor.
Now you need to open the module where you are going to copy code to, copy it, then open the new module and paste in the code.
Because VBA is just text, you could use this method to paste in code sent to you by email or in a text document, or copied off a web page.
VBA code is contained in a code module. Forms and their code are in code modules.
Code can be exported as .bas files, forms (and their code) as .frm files. These files can be imported to other workbooks, or sent to people and they can import them to their own workbooks.
Right click the module you want to export and choose Export File. Browse to the folder you want to save the file into, change the name of the file you are saving if you wish, and then click Save.
Right click in the project you want to import to, choose Import File. Browse to locate the file you want, and click Open.
Drag & Drop Modules
This is the quickest and easiest way to get modules and forms from one workbook to another.
Click on the module with your mouse and hold the mouse button down. Drag the mouse pointer to the workbook (VBA Project) you want to copy the code to, then release the mouse button.
Running Macros from PERSONAL.XLSB
Once you’ve gotten the code and/or forms into PERSONAL.XLSB, they are available to use in any workbook. Press ALT+F8 to see the macros.
You can now add an icon to your QAT to run the macro.
Now you know how to get code into your PERSONAL.XLSB, give it a go with some of these: