?
Solved

Share Macro to all spreadsheets on workstation

Posted on 2013-12-21
2
Medium Priority
?
443 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 1600 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 400 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
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 …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

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