The case of the disappearing macro

[Paul_B]

I'm having difficulty understanding how templates relate to .xls
docs. For instance, I created a new doc which very evidently is
based on my book.xlt file in the xlstart folder. Yet when I go to
File/Properties the template designation is grayed out.

Then I record a simple macro. The macro is available via
Tools/Macro/Run, but when I want to put it up on a toolbar, it's
nowhere to be found under the Customization dialog.

I must be missing something basic here.

Thanks,
p.

 

[Dave Peterson]

Excel works differently than word.

Once the new workbook is created, there is no connection back to the template
file.

If you want macros available whenever you start excel, you can put them in a
workbook in your XLStart folder--most people call this file personal.xls.

If you want to give the user (you???) a way to access those macros, you could
use one of these:

(saved from a previous post)

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

 

[Paul_B]

Excel works differently than word.

Once the new workbook is created, there is no connection back to the template
file.

If you want macros available whenever you start excel, you can put them in a
workbook in your XLStart folder--most people call this file personal.xls.

I am seeing the macros I've created and stored in book.xlt. It's
just that they're not available for UI Customization. Yet the
template not being named under File/Properties seems to support
what you're saying.

If you want to give the user (you???) a way to access those macros, you could
use one of these:

(saved from a previous post)

For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm

Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)

Ok, I found a way to do what I want. I opened the book.xlt
template, went Customize/Macro, and dragged 'Custom Menu Item' to
the toolbar. Right clicking it allows assigning a macro. When I
saved the template the button was subsequently part of new
spreadsheets.

It also was part of existing spreadsheets, so evidently the
connection of the spreadsheet to the foundational template
persists, even though that connection is not acknowledged under
File/Properties. I still don't understand that.

Thanks,
p.

 

[Dave Peterson]

Most people don't put macros in their book.xlt files. This template file is
usually set up to for much more mundane things--number of worksheets in the
workbook, headers/footers--but not macros.

Any workbook that is created using book.xlt as the template file would inherit
the macros. For the most part, this would be overkill. I know that I want only
one version of my macros so that I don't have to update 1000's of copies when
something changes.

 

[Dave Peterson]

ps. The toolbar isn't associated with a workbook (unless you do something
special). And the macros that are assigned to that toolbar can point back to
the book.xlt template file. But when you click on an icon, excel will open
book.xlt (if it isn't open).

Most people will put these common macros in a dedicated workbook and put it in
their XLStart folder. But I'm repeating myself....