Where to save macros

[Raewyn]

I have two questions really. First, I was told there was a specific place to
save a macro if I wanted it to be avalable whenever I opened excel. Where
would that be? Also, how do I create a macro that is always available when I
open excel instead of spreadsheet specific. Thanks for your help.

[Don Guillett]

If you do not already have a personal.xls >record a macro to personal macro
workbook. When you close excel after that creation you will be asked if you
want to save personal.xls >say yes. Now it will be available to you.
However this is a hidden workbook.

--
Don Guillett
SalesAid Software
donaldb@281.com
"Raewyn" <Raewyn@discussions.microsoft.com> wrote in message
news:CD7FCC3E-B051-4B22-8F41-C953FC731B44@microsoft.com...
> I have two questions really. First, I was told there was a specific place
to
> save a macro if I wanted it to be avalable whenever I opened excel. Where
> would that be? Also, how do I create a macro that is always available when
I
> open excel instead of spreadsheet specific. Thanks for your help.

[Gord Dibben]

Rae

Maybe Personal.xls which is a workbook that holds your global macros so's they
are available for all open workbooks?

Personal.xls is created the first time you record a Macro using Macro
Recorder.

Tools>Macro>Record New Macro. A dialog box will come up asking you name the
macro and where to place it. Pick Personal Macro Workbook from the dropdown.
Copy and paste a couple of cells then Stop Recording.

You now have a Personal.xls in your Office\XLSTART folder. You can go to
Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module.

You can add more macros by recording or by typing/copying them into the
Module.

You can do a File>Save from there or better yet hit ALT + Q to return to the
Excel window.

Then with Personal.xls active, hit Window>Hide.

When you close Excel you will be asked if you want to save Personal.xls. Yes!

It will open hidden next time you start Excel.

NOTE: when assigning macros to buttons or menu items you will have to precede
the macro name with Personal.xls.

i.e. Personal.xls!macroname

An alternative to Personal.xls is to create an add-in(*.xla) with your macros
in it and load it through Tools>Add-ins.

The benefit of this is that you don't have to precede the macro name with the
filename.

A disadvantage is that you will not see the macros in the Tools>Macro>Macros
dialog.


Gord Dibben Excel MVP


On Thu, 28 Apr 2005 07:32:03 -0700, Raewyn <Raewyn@discussions.microsoft.com>
wrote:

>I have two questions really. First, I was told there was a specific place to
>save a macro if I wanted it to be avalable whenever I opened excel. Where
>would that be? Also, how do I create a macro that is always available when I
>open excel instead of spreadsheet specific. Thanks for your help.

[elli]

Don, could we take this one step further please. New computer and it's been
a long while since I've had to start from scratch.
When I try to record a macro, I get the message "Personal Macro Workbook in
the startup folder must stay open for recording". All I have in my XLSTART
folder is Personal.xls, that automatically opens when I enter excel, so it is
open. I click OK, hoping to record a macro to "create" the folder. I get is
"Unable to record". There is no file in Window - Unhide. Am I looking in the
wrong place? Elli

"Don Guillett" wrote:

> If you do not already have a personal.xls >record a macro to personal macro
> workbook. When you close excel after that creation you will be asked if you
> want to save personal.xls >say yes. Now it will be available to you.
> However this is a hidden workbook.
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Raewyn" <Raewyn@discussions.microsoft.com> wrote in message
> news:CD7FCC3E-B051-4B22-8F41-C953FC731B44@microsoft.com...
> > I have two questions really. First, I was told there was a specific place
> to
> > save a macro if I wanted it to be avalable whenever I opened excel. Where
> > would that be? Also, how do I create a macro that is always available when
> I
> > open excel instead of spreadsheet specific. Thanks for your help.
>
>
>