Solved

Share Macro to all spreadsheets on workstation

Posted on 2013-12-21
2
440 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 400 total points
ID: 39733479
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:Gregory Miller
Gregory Miller earned 100 total points
ID: 39733497
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

726 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