Finding the personal macro workbook

[csenor]

I'm trying to make my macros available to all of my excel projects. I've already read two articles from Microsoft Support, but they don't help. Every time I've made typed code, I have just opened up the Visual Basic icon in Excel. I have never used the "Record Macro" button. when I try to save the macro to another location, it doesn't give you the option to put it in your personal macro workbook. This is how the tree view looks in my Visual Basic:

VBAProject(Pawn Shop Excel.xlsm)
Microsoft Excel Objects
Sheet1
Sheet 2
This Workbook
Forms
Modules
Module1
Module2

Any help would be appreciated. My thought was that Pawn Shop Excel.xlsm should somehow be named Personal. Pawn Shop Excel is the name of the workbook open.

 

[rjwebgraphix]

Look at the .xla extension. That's Excel Add-in. You can call it whatever you want. It doesn't have to be personal.xla. It could be MySuperDuperTerrifcMacros.xla as long as its an xla extension. Then save it to the Add-in folder. On my system that is....

C:\Documents and Settings\LocalAdmin\Application Data\Microsoft\AddIns

...although your system could be a different folder.

Once your macros are saved in the Add-Ins and have been included in available addins. From Excel Worksheet click Tools - Add-Ins

Then make sure your add-in is checked. Then you should be able to run those macros on any workbook. Just keep in mind that the code will have to be written as if its in a different workbook, so a typical recorded macro probably wouldn't work right without the right workbook declarations and references.

 

[csenor]

I don't see .xla file extensions. I'm running Windows 7 and Office 2010. When I'm looking at the ribbon in Excel at Add-ins, do I need to check any of the 4 boxes?

 

[Firefly2012]

Can you click Record Macro, choose to save in Personal Macro Workbook (one will be created for you), Stop Recording, and then just move your required code from its current location into the Personal.xlsb that will have been created? You need to save the changes to the PMW when you exit Excel.

 

[rjwebgraphix]

I don't see .xla file extensions. I'm running Windows 7 and Office 2010. When I'm looking at the ribbon in Excel at Add-ins, do I need to check any of the 4 boxes?

I cannot say anything to Win7 or XL10. In XL03 a simple File-Save As- Save As Type - Microsoft Office Excel Add-in (*.xla) is the place to be. Although, since you're running a later version, there may be options available to you other than this. I'm not familiar enough with it to be able to tell you one way or the other.