Most macros I have created (and set up a button for on my toolbar) work just
fine. However, occasionally I create a macro that will only work by opening
the specific file I was working in when I created it. I cannot figure out
the distinction between the two scenarios, nor can I even find the macros
that are working fine without opening additional files.

I tried creating a macro in the default book.xlt template (which, amazingly,
had no macros in it), but even then, Excel wants to open book.xlt everytime I
want to run the macro.

How can I create macros and be sure they will work in all workbooks without
opening additional workbooks?

You can't be sure they will work in all workbooks because an component the
macro looks for may not be present but macros can be available in all
workbooks if they are saved to Personal.xls. This could create a problem for
other users who would then not have access to the macro.

Mike

"opieandy" wrote:

Most macros I have created (and set up a button for on my toolbar) work just
fine. However, occasionally I create a macro that will only work by opening
the specific file I was working in when I created it. I cannot figure out
the distinction between the two scenarios, nor can I even find the macros
that are working fine without opening additional files.

I tried creating a macro in the default book.xlt template (which, amazingly,
had no macros in it), but even then, Excel wants to open book.xlt everytime I
want to run the macro.

How can I create macros and be sure they will work in all workbooks without
opening additional workbooks?

There is no "personal.xls" file on my work pc. There is a "Book.xlt", which
houses other specific preferences I've selected (default number format, font,
etc.).

On my home PC, "personal.xls" does house all my macros, and opens every time
I open any Excel file, whether I run a macro or not, which is aggravating.

I am not sharing any macros with other users, so that is not a concern. I
just want to understand why some work globally across all files and others
don't. The specific macro in question is a very simple one that inserts the
file name into the bottom of the sheet. (Cell="filename", A1). The macro
works, but I don't understand why it needs to open a specific file for it to
be used.

Chris


"Mike H" wrote:

You can't be sure they will work in all workbooks because an component the
macro looks for may not be present but macros can be available in all
workbooks if they are saved to Personal.xls. This could create a problem for
other users who would then not have access to the macro.

Mike

"opieandy" wrote:

Most macros I have created (and set up a button for on my toolbar) work just
fine. However, occasionally I create a macro that will only work by opening
the specific file I was working in when I created it. I cannot figure out
the distinction between the two scenarios, nor can I even find the macros
that are working fine without opening additional files.

I tried creating a macro in the default book.xlt template (which, amazingly,
had no macros in it), but even then, Excel wants to open book.xlt everytime I
want to run the macro.

How can I create macros and be sure they will work in all workbooks without
opening additional workbooks?

Here's another tidbit. I deleted one of the macros that was working globally
without opening a source workbook. I re-created that macro in "book.xlt".
Now that macro will only run by opening "book.xlt" whereas before it ran fine
without opening another file. This runs counter to the theory that 'a
component the macro looks for may not be present', so there must be some
other answer. And now I've got 2 macros that will only work by opening the
source workbook. :(

"opieandy" wrote:

There is no "personal.xls" file on my work pc. There is a "Book.xlt", which
houses other specific preferences I've selected (default number format, font,
etc.).

On my home PC, "personal.xls" does house all my macros, and opens every time
I open any Excel file, whether I run a macro or not, which is aggravating.

I am not sharing any macros with other users, so that is not a concern. I
just want to understand why some work globally across all files and others
don't. The specific macro in question is a very simple one that inserts the
file name into the bottom of the sheet. (Cell="filename", A1). The macro
works, but I don't understand why it needs to open a specific file for it to
be used.

Chris


"Mike H" wrote:

You can't be sure they will work in all workbooks because an component the
macro looks for may not be present but macros can be available in all
workbooks if they are saved to Personal.xls. This could create a problem for
other users who would then not have access to the macro.

Mike

"opieandy" wrote:

Most macros I have created (and set up a button for on my toolbar) work just
fine. However, occasionally I create a macro that will only work by opening
the specific file I was working in when I created it. I cannot figure out
the distinction between the two scenarios, nor can I even find the macros
that are working fine without opening additional files.

I tried creating a macro in the default book.xlt template (which, amazingly,
had no macros in it), but even then, Excel wants to open book.xlt everytime I
want to run the macro.

How can I create macros and be sure they will work in all workbooks without
opening additional workbooks?

I don't think I'd recommend anyone putting macros in book.xlt. This file is
used as a template file when you're creating a new workbook.

And I can't imagine that having the same macro in every workbook would be a good
idea--just from a standpoint of organization.

I'd put all those macros in a single workbook and store that workbook in my
XLStart folder. Lots of people name this workbook personal.xls--but you can
call it anything you want.

Then assign the buttons to that macro. As long as the file is in XLStart, it'll
open when you start excel. And the buttons will be assigned to macros in that
workbook.

When you do this, you will have to reassign those buttons to the macros in that
workbook.

opieandy wrote:

Here's another tidbit. I deleted one of the macros that was working globally
without opening a source workbook. I re-created that macro in "book.xlt".
Now that macro will only run by opening "book.xlt" whereas before it ran fine
without opening another file. This runs counter to the theory that 'a
component the macro looks for may not be present', so there must be some
other answer. And now I've got 2 macros that will only work by opening the
source workbook. :(

"opieandy" wrote:

There is no "personal.xls" file on my work pc. There is a "Book.xlt", which
houses other specific preferences I've selected (default number format, font,
etc.).

On my home PC, "personal.xls" does house all my macros, and opens every time
I open any Excel file, whether I run a macro or not, which is aggravating.

I am not sharing any macros with other users, so that is not a concern. I
just want to understand why some work globally across all files and others
don't. The specific macro in question is a very simple one that inserts the
file name into the bottom of the sheet. (Cell="filename", A1). The macro
works, but I don't understand why it needs to open a specific file for it to
be used.

Chris


"Mike H" wrote:

You can't be sure they will work in all workbooks because an component the
macro looks for may not be present but macros can be available in all
workbooks if they are saved to Personal.xls. This could create a problem for
other users who would then not have access to the macro.

Mike

"opieandy" wrote:

Most macros I have created (and set up a button for on my toolbar) work just
fine. However, occasionally I create a macro that will only work by opening
the specific file I was working in when I created it. I cannot figure out
the distinction between the two scenarios, nor can I even find the macros
that are working fine without opening additional files.

I tried creating a macro in the default book.xlt template (which, amazingly,
had no macros in it), but even then, Excel wants to open book.xlt everytime I
want to run the macro.

How can I create macros and be sure they will work in all workbooks without
opening additional workbooks?

--

Dave Peterson

Thanks, Dave. Only problem is, I have no idea where some of the macros are.
As I mentioned, though each workbook shows that it contains no macros, the
macro toolbar buttons are working globally. It is a mystery where the
underlying macro is saved and why they are being accessed without opening
files. I really would like to understand that. I certainly don't want to
try to hunt down all the macros that are working (to input them into a
personal.xls file) for the sake of fixing the two that aren't.

Chris


"Dave Peterson" wrote:

I don't think I'd recommend anyone putting macros in book.xlt. This file is
used as a template file when you're creating a new workbook.

And I can't imagine that having the same macro in every workbook would be a good
idea--just from a standpoint of organization.

I'd put all those macros in a single workbook and store that workbook in my
XLStart folder. Lots of people name this workbook personal.xls--but you can
call it anything you want.

Then assign the buttons to that macro. As long as the file is in XLStart, it'll
open when you start excel. And the buttons will be assigned to macros in that
workbook.

When you do this, you will have to reassign those buttons to the macros in that
workbook.

opieandy wrote:

Here's another tidbit. I deleted one of the macros that was working globally
without opening a source workbook. I re-created that macro in "book.xlt".
Now that macro will only run by opening "book.xlt" whereas before it ran fine
without opening another file. This runs counter to the theory that 'a
component the macro looks for may not be present', so there must be some
other answer. And now I've got 2 macros that will only work by opening the
source workbook. :(

"opieandy" wrote:

There is no "personal.xls" file on my work pc. There is a "Book.xlt", which
houses other specific preferences I've selected (default number format, font,
etc.).

On my home PC, "personal.xls" does house all my macros, and opens every time
I open any Excel file, whether I run a macro or not, which is aggravating.

I am not sharing any macros with other users, so that is not a concern. I
just want to understand why some work globally across all files and others
don't. The specific macro in question is a very simple one that inserts the
file name into the bottom of the sheet. (Cell="filename", A1). The macro
works, but I don't understand why it needs to open a specific file for it to
be used.

Chris


"Mike H" wrote:

You can't be sure they will work in all workbooks because an component the
macro looks for may not be present but macros can be available in all
workbooks if they are saved to Personal.xls. This could create a problem for
other users who would then not have access to the macro.

Mike

"opieandy" wrote:

Most macros I have created (and set up a button for on my toolbar) work just
fine. However, occasionally I create a macro that will only work by opening
the specific file I was working in when I created it. I cannot figure out
the distinction between the two scenarios, nor can I even find the macros
that are working fine without opening additional files.

I tried creating a macro in the default book.xlt template (which, amazingly,
had no macros in it), but even then, Excel wants to open book.xlt everytime I
want to run the macro.

How can I create macros and be sure they will work in all workbooks without
opening additional workbooks?

--

Dave Peterson