• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Moving VBA Code and Forms Into PERSONAL.XLSB

You are here: Home / Excel VBA / Moving VBA Code and Forms Into PERSONAL.XLSB
Moving VBA Code and Forms Into PERSONAL.XLSB
July 28, 2016 by Philip Treacy

After you’ve written some VBA, or created your own form, or you’ve found some useful code on the internet, you might want to put this into your PERSONAL.XLSB.

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.

Form Code

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.

viewing form 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

userform design

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.

clicking delete control on userform

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.

manually insert modules to copy paste 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.

Export/import

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.

Export

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.

Import

Right click in the project you want to import to, choose Import File. Browse to locate the file you want, and click Open.

export and import code and form modules

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.

drag and drop vba code and form modules

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.

macros in personal.xlsb

You can now add an icon to your QAT to run the macro.

More Macros

Now you know how to get code into your PERSONAL.XLSB, give it a go with some of these:

Hide, Delete & Sort Sheets With a Form

Maximize Excel Across Multiple Screens

Highlight, Text, Numbers, Dates & Formulas

Creating Excel Forms

Create a Hyperlinked List of Files from Your Folders

Create PDF from Worksheet and Email It

Moving VBA Code and Forms Into PERSONAL.XLSB

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

Display All Matches from Search in Userform ListBox

Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts

Animating Excel Charts

Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms

Dynamic Data Validation Lists in Userforms

Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables

Show Report Filter Pages for Power Pivot PivotTables

PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel

Charting Real Time Data in Excel

Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list

Select Multiple Items from Drop Down (Data Validation) List

Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms

Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports

Automating and Emailing Pivot Table Reports

Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform

Searching for Data With a User Form

Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.




Category: Excel VBA
Previous Post:Hide, Delete, Sort Sheets with VBA in ExcelHide, Delete and Sort Sheets With VBA Userform
Next Post:Excel Chart ColorsExcel Chart Colors

Reader Interactions

Comments

  1. Carter H.

    October 2, 2016 at 10:02 am

    Very descriptive and oh, so easy to follow! Thanks so much for these tips!

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.