"Global" Excel Macro 1

[stnkyminky]

I need to write a macro that will be available when Excel starts up. This macro should not be associated with just one spreadsheet (workbook) but can be applied to any .xls file. Scott
Programmer Analyst
<><

 

[cheerio]

In an Excel workbook go to Tools, Macro, record new macro.

Now in the drop down box where it says store macro in select Personal Macro Workbook.

Record a very simple macro and save.

What you have now done is create an Excel workbook called Personal.xls that will be stored in your Excel startup folder. It will be opened everytime you open Excel as a hidden workbook.

Obviously you could create the book yourself from first principles but my way you do not have to think where the Excel startup folder is.

Also obviously the macros will be available only when the personal workbook is open. So if you take your spreadsheets to another machine they will not be available to you (unless the startup folder is a network folder that you can see from both machines).

Ken

 

[stnkyminky]

I have tried your solution and it works perfectly. Is there a way to reference the personal.xls file if it resides on a drive that is public to all users? Ideally I would like to create the macro and have several users using it. Can this be done without creating a personal.xls on every machine?

The startup folder will reside locally on each machine. Scott
Programmer Analyst
<><

 

[stnkyminky]

Almost forgot your star....sorry Scott
Programmer Analyst
<><

 

[cheerio]

The star is appreciated.

Excel can have two startup locations and this is designed to allow you to store some startup files locally and others in a shared area,

If you go to Tools, Options, General tab you can set an Alternate Startup File Location.

If you do this I would suggest making the file read only otherwise different users would be trying to open the file at the same time with read/write permissions which could cause problems.

Ken