Making macro work in all workbooks

  • Thread starter Thread starter dderolph
  • Start date Start date
D

dderolph

How do I record a macro so that it will run when any workbook is opened
I'm thinking it has to be saved in a file named personal.xls and save
in F:\Program Files\Microsoft Office\OFFICE11\XLSTART. At the presen
time, I have nothing in that folder; I thought it was supposed t
always have a file named personal.xls.

Can someone advise me on this
 
Do you really mean you want to have a macro run when you open any workbook?

Or are you asking how to have macros available when any workbook is open--and
you can run them on demand?

If the first, you'll need some application level events. Visit Chip Pearson's
site:
http://www.cpearson.com/excel/AppEvent.htm
to learn more about them.

If you mean the second, then personal.xls won't exist until you create it. And
recording a macro into personal.xls is one way to create this.

Tools|macro|record new macro
You'll see a dropdown that asks where to store it.
You can choose "personal macro workbook"
 
I meant the second situation. After reading your message, I recorde
the macro per your procedure and it's works right, now.

I see personal.xls is in F:\Documents and Settings\UserName\Applicatio
Data\Microsoft\Excel\XLSTART, not F:\Program Files\Microsof
Office\OFFICE11\XLSTART. Apparently, two XLSTART folders are normal.
Can you confirm that?

Thanks for your help
 
I think it depends on your upgrade path.

When I upgraded from xl97 to xl2002 (skipping xl2k), I found the
"real" version, moved my files there and deleted the files from older
version.

It may not make a difference to excel, but it makes my head spin less!
 
I've got a second computer with Win 98SE and Office 97. I see the
personal.xls file is in C:\PROGRAM FILES\MICROSOFT
OFFICE\OFFICE\XLStart.

My installation of Office 2003 was a full version and, again, put the
personal.xls file in F:\Documents and Settings\UserName\Application
Data\Microsoft\Excel\XLSTART.

I guess that's just the way it is.
 
Even though xl will find the other XLStart folders, you can find the "real" one
by:

Opening excel
hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window

Type this and hit enter:
?application.StartupPath

For me (xl2002 and win98), I get:
C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART

But as long as you don't put it in both, excel is very forgiving.
 
Back
Top