How do I make it so my macro is in every new excel file?

[mackaymonkey]

Hi. I've written a quick macro for the employees here, but I don't know how to make it so that the macro is there automatically when they open a new document. I just want to make it so all they have to do is run it.

any help?
thanks,
Nick

 

[Wray69]

Save it to your personal.xls, or place it in the XLSTART folder.

Cheers,

Wray

 

[mackaymonkey]

I have tried saving it to xlstart, but it doesn't do anything. also, i do not have a personal.xls file anywhere.

 

[susejdrol]

When you create a new macro, there is a drop down box that asks you where you want to save it. One of the options there is your personal macro workbook which is the personal.xls file they are refering to.

Hope that helps.

CP

 

[combo]

What action is your macro to do? If the user is to have access to it, personal.xls is the solution.

If you want the macro to run automatically when a new workbook is created, application events have to be turned on. To do this (see also xl help files):
1. add class module (here named clsApp) with:
[tt]Public WithEvents AppEvents As Application
Private Sub AppEvents_NewWorkbook(ByVal Wb As Workbook)
' your macro here with Wb reference to the workbook created
End Sub[/tt]
2. initialize application events in standard module (this macro must be run first):
[tt]Dim appEvents As New clsApp
Sub init()
Set appEvents.AppEvents = Application
End Sub[/tt]

For a better control, you can create an add-in and place the above code in it (simply save workbook with full code as excel add-in). To initialize macro automatically, in it's ThisWorkbook module add code:
[tt]Private Sub Workbook_Open()
Call init
End Sub[/tt]
When you install the add-in (tools>add-ins -> browse) it will be opened with excel, Workbook_Open macro will be executed and events initialized. It can be easily switched-off by unchecking the add-in in the add-ins window.

combo

 

[PeterMoran]

Hi Nick,

The simplest way to do what you want is to create a template called BOOK.XLT and include the macro in that and then save it to your XLSTART folder. You will need to copy the template to every users XLSTART folder.

BOOK.XLT, if present as indicated, is the template used whenever a new Excel file is created, and can contain standard headers and footers etc etc, plus any macros needed when a new file is created.

Good Luck!

Peter Moran
Two heads are always better than one!