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

I would take the time to do that search. Someday, you'll be glad you did.

If you open excel and then
tools|Customize (just to see that dialog)
rightclick on each of the toolbar icons that you've made
select Assign macro
You should see what macro is assigned to that button/icon.

Someday, you'll be much happier if you can find the macro so that you can update
it or even refer to it.

And if you're going to do this work, your life will become much simpler if you
include code to create the toolbar when the workbook is opened and include code
to destroy the toolbar when the workbook is closed.

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)

opieandy wrote:

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


--

Dave Peterson

The issue is that when I right click and choose 'assign macro', the macro
pop-up box is blank. It as if no macro is assigned. Yet the toolbar button
works perfectly. So I have no idea how to find those macros.

"Dave Peterson" wrote:

I would take the time to do that search. Someday, you'll be glad you did.

If you open excel and then
tools|Customize (just to see that dialog)
rightclick on each of the toolbar icons that you've made
select Assign macro
You should see what macro is assigned to that button/icon.

Someday, you'll be much happier if you can find the macro so that you can update
it or even refer to it.

And if you're going to do this work, your life will become much simpler if you
include code to create the toolbar when the workbook is opened and include code
to destroy the toolbar when the workbook is closed.

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)

opieandy wrote:

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


--

Dave Peterson

Are you sure it's a macro?

You can add built-in commands to any toolbar via the tools|Customize dialog.

If you're sure it's a macro, create a new workbook (close all the others).
Click that button.
Now hit alt-f11 to see the VBE
hit ctrl-r to see the project explorer.
Make a note of all the projects that are now open.

One of them should look familiar.
You should see something like:
VBAProject (book1.xls)

Now hit ctrl-g to see the immediate window.
Type this and hit enter:
?workbooks("book1.xls").fullname

And you should see the drive/path and name of that file.

opieandy wrote:

The issue is that when I right click and choose 'assign macro', the macro
pop-up box is blank. It as if no macro is assigned. Yet the toolbar button
works perfectly. So I have no idea how to find those macros.

"Dave Peterson" wrote:

I would take the time to do that search. Someday, you'll be glad you did.

If you open excel and then
tools|Customize (just to see that dialog)
rightclick on each of the toolbar icons that you've made
select Assign macro
You should see what macro is assigned to that button/icon.

Someday, you'll be much happier if you can find the macro so that you can update
it or even refer to it.

And if you're going to do this work, your life will become much simpler if you
include code to create the toolbar when the workbook is opened and include code
to destroy the toolbar when the workbook is closed.

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)

opieandy wrote:

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


--

Dave Peterson


--

Dave Peterson

Maybe your macros are declared as Private

Private Sub Foo()

These won't show in the pop-up when you "assign macro".

Also any macros that would be in an Add-in would not show.

Just type the name of the macro and hit "Edit" to go find it.


Gord Dibben MS Excel MVP


On Wed, 23 May 2007 13:24:01 -0700, opieandy
wrote:

The issue is that when I right click and choose 'assign macro', the macro
pop-up box is blank. It as if no macro is assigned. Yet the toolbar button
works perfectly. So I have no idea how to find those macros.

"Dave Peterson" wrote:

I would take the time to do that search. Someday, you'll be glad you did.

If you open excel and then
tools|Customize (just to see that dialog)
rightclick on each of the toolbar icons that you've made
select Assign macro
You should see what macro is assigned to that button/icon.

Someday, you'll be much happier if you can find the macro so that you can update
it or even refer to it.

And if you're going to do this work, your life will become much simpler if you
include code to create the toolbar when the workbook is opened and include code
to destroy the toolbar when the workbook is closed.

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)

opieandy wrote:

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


--

Dave Peterson