Problem in Excel 2010 when trying to publish macros inside an .XLTM file

[Jtorres13]

We currently have a .DOTM file in %programfiles(x86)%\Microsoft Office\Office14\STARTUP. When users open Word, the .DOTM file opens in the background and the macros are available when user hits Alt+F8. The .DOTM opens "protected" in a way users cannot edit any of the macros in it.

I want to do the same thing, but in Excel. I created 3 simple macros to open 3 Excel templates. The templates can be opened from File | New | My Templates, but the HR person requesting this, wants to open them via macros. I want to make these Excel macros available to all users in my office by just sharing the .XLTM workbook with them. So, I put the .XLTM file in %programfiles(x86)%\Microsoft Office\Office14\XLSTART, open Excel. When I hit Alt+F8, the macros organizer is empty.

So, I figured I had put the .XLTM file in the wrong folder. I then put it in %Appdata%\Microsoft\Excel\XLSTART. It doesn't work from there either! I googled and found the thing about the PERSONAL.XLSB. I thought, great, just save my macros file as another .XLSB and put it inthe same folder with my other one. NOPE!! Excel title bar now reads "Macros.XLSB" instead of "Book 1." It's opening the .XLSB as a regular file, not "in the background."

All I want is to open Excel and see "Book 1" in the title bar and to get my three macros when I hit Alt+F8. The macros have to be in a different file, not in PERSONAL.XLSB because some users already have custom macros they created. I just want to add these 3 new macros to the pot, like we did in Word. Another reason for having them in a separate file is to make it easy to edit/add macros and deploy them by distributing the file via login script or GPO or something like that.

Can this be done?

 

[SkipVought]

hi,

How did you create your PERSONAL.xlsm workbook?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Jtorres13]

I I have created other macros for personal use and saves them there so they are available in all my workbooks.

 

[SkipVought]

The macros have to be in a different file, not in PERSONAL.XLSB because some users already have custom macros they created.

I published a MODULE to a user group on a regular basis, that contained about 100 functions and procedures for their use.

Gave them instructions about how to get the latest version from the network and IMPORT into their PERSONAL.xlsb workbook. It's a fairly easy import process.

AFAIK you ought only to have the personal.xlsb workbook in XLSTART.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Jtorres13]

Can't do that. Is there no way to have macros in a file other than PERSONAL.XLSB? What about as an add-in (.XLAM)?

 

[SkipVought]

Not and have the macros available in the background, AFAIK.

Maybe an Add-In would help. I don't have any experience with constructing add-ins. Maybe some other member could help.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

VBA add-in is a nice feature to pack and distribute pieces of your code. It's a regular workbook marked as add-in that adds some extra features and removes UI interface.
Just create a workbook with code, protect VBA project, add title (future name of the add-in) and comments (add-in's description) to workbook features, save it, and next save it again as add-in.
The add-in has to be installed - i.e. made visible as available add-in, and activated - i.e. opened in the background.
You can find some info in MS website, better info is here:

http://peltiertech.com/installing-an-excel-add-in/

combo