Solved

Share Macro to all spreadsheets on workstation

Posted on 2013-12-21
2
433 Views
Last Modified: 2014-01-03
I have written a VBA macro in Excel which I need make available to any spreadsheet I open.  how can I do this?  We are using Excel 2007 on XP and the user downloads .xlsx spreadsheets which she will need to run the macro on.
0
Comment
Question by:canuckconsulting
2 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 400 total points
Comment Utility
If you put a macro in your Personal.xlsb macro workbook, then it will be available to any other spreadsheet. The Personal.xlsb workbook is opened automatically whenever Excel launches.

First, check to see if you already have one. To do this:
1.  ALT + F11 to open the VBA Editor
2.  Look in the Project Explorer pane on the left for a VBA Project named Personal.xlsb
3.  If you see it, click the + sign next to Modules under Personal.xlsb
4.  Paste the code in one of those module sheets

If you don't have a Personal.xlsb, it is easy to create one:
1.  Use the Macro Recorder to record a macro
Macro Recorder button2.  In the first step of the wizard, choose to store the macro in "Personal Macro Workbook"
Store macro in Personal macro workbook3.  Stop recording the macro (it doesn't have to do anything). You do this by clicking the same Macro Recorder button as in step 1.
4.  Copy code for any macros you want to be available all the time into a module sheet in the newly created Personal.xlsb workbook
0
 
LVL 11

Assisted Solution

by:Technodweeb
Technodweeb earned 100 total points
Comment Utility
Here are some notes on where to find the Personal Macro Workbook since you have an earlier version than the example of 2013 above.
http://wheatblog.com/2011/08/where-is-the-excel-personal-macro-workbook-located/
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup". After a while, you have entered a loop for Auto repair which does not fix anything and you will be in a  panic as all your work w…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now