The case of the disappearing macro

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul_B - 13 Dec 2006 01:11 GMT

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.

Signature

MS Office '03

Reply to this Message

Dave Peterson - 13 Dec 2006 02:26 GMT

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)

> I'm having difficulty understanding how templates relate to .xls
> docs. For instance, I created a new doc which very evidently is
[quoted text clipped - 11 lines]
> --
> MS Office '03

Signature

Dave Peterson

Reply to this Message

Paul_B - 13 Dec 2006 02:52 GMT

> Excel works differently than word.
>
[quoted text clipped - 3 lines]
> 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:
[quoted text clipped - 8 lines]
> 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.

Signature

MS Office '03

Reply to this Message

Dave Peterson - 13 Dec 2006 03:09 GMT

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.

> > Excel works differently than word.
> >
[quoted text clipped - 38 lines]
> --
> MS Office '03

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 13 Dec 2006 03:12 GMT

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....

> > Excel works differently than word.
> >
[quoted text clipped - 38 lines]
> --
> MS Office '03

Signature

Dave Peterson

Reply to this Message

Rate this thread: