Thanks for the suggestions, guys. After a bunch of research I found that, for Excel macros to be shared across all workbooks, they have to be saved to a spreadsheet called Personal.xls, which resides as a "hidden" workbook in a folder called XLSTART in the Excel application data folder. (So, for example, in my case it's "C:\Documents and Settings\Scott\Application Data\Microsoft\Excel\XLSTART\personal.xls".)
Now this is all well and good if you're performing a "record macro" routine, but if you have a bunch of VB code that you want to just paste and save, you have to jump through hoops. First I had to open a blank workbook and "record a macro" (no keystrokes, just start the recording and end it) and tell Excel I wanted to save it in Personal.xls. Then I had to open Personal.xls and "unhide" the workbook. Only THEN could I open the macro for editing and paste the code, then I had to "hide" Personal.xls again, then save it to disk.
Once all that was done, the macro was available whenever I opened a new workbook in Excel. The first time I forgot to "hide" Personal.xls, so that every time I fired up Excel, Personal.xls came up as the default workbook (instead of the usual "Book1.xls".) Not really a problem because I "save as" and rename every new spreadsheet I create, but it bugged me that it mutated like that.
So there we go, what a pain, but it taught me how to fish, so I'll eat for a lifetime. In the future I'll just edit Personal.xls directly. Thanks again for the suggestions.
|