Book.xlt

W

Wazooli

I have made several modifications to my Book.xlt, including some VBA code and
custom buttons with macros assigned to them. The problem is that when I hit
one button that is linked to code in module 1, it always opens Book.xlt to
acccess the macro. What am I doing wrong. I only want Book.xlt to open if I
want to edit it.

On a separate but related issue, what code can I use to simultaneously save
Book.xlt, when changed, to each of 4 separate locations?

wazooli
 
G

Gord Dibben

Wazooli

BOOK.XLT is a Template upon which new workbooks are based.

I think you should keep macros out of there.

Instead place your macros in Personal.xls which can open in a "hidden" state.

The customizations to menus and buttons can be made on the fly when
Personal.xls opens.

Or you can make the customizations to a regular menus and toolbars,

These customizations are saved in an *.XLB file so they are available for
every workbook.

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
 
Top