How does XL "lose" a macro that's right there?

[salgud]

Working on a macro for a while. I have a tool on a toolbar assigned to that
macro. It worked just fine until this morning. When I click on the button,
I get an error, "The macro "Tribal TR Template V1.xls'!Create TR' cannot be
found". Yet the worksheet with that name and the macro with that name are
both there, right in front of me.

I've gone in and reassigned that button to that macro, which is there in
the list of macros, and I still get the same message. I created another
button on another toolbar and assigned it to this macro and still get the
same message!

Does anyone know what this could be?

 

[Bob Umlas]

You can't have a macro named
Create TR
blanks are not allowed in macro names.
Is that the issue?

 

[salgud]

Working on a macro for a while. I have a tool on a toolbar assigned to that
macro. It worked just fine until this morning. When I click on the button,
I get an error, "The macro "Tribal TR Template V1.xls'!Create TR' cannot be
found". Yet the worksheet with that name and the macro with that name are
both there, right in front of me.

I've gone in and reassigned that button to that macro, which is there in
the list of macros, and I still get the same message. I created another
button on another toolbar and assigned it to this macro and still get the
same message!

Does anyone know what this could be?

I found the problem, though I haven't a clue as to how this could happen.
The macro had a compile error in it where I was modifying it. Since I
didn't know how to solve the problem of it not running from the button, I
decided to run it from the macro list until I solved this problem. I fixed
the compile error, and, voila! The macro then ran from the toolbar button!
I dont' understand this at all. Shouldn't a call to run the macro from a
toolbar button give the same result as selecting the macro to run from the
macro list? Why did executing the macro from the button give me a message
saying the macro couldn't be found, and running it from the macro list gave
me the compile error?

 

[Luke M]

It may be thinking your macro is invalid. How do you have a space in the name
of your macro? I do not have XL2007, but all the macros I've seen need a name
like

CreateTR
or
Create_TR

Note that this may not be the case, and may instead be a feature that 2007
allows. I've just never seen it before.

 

[Tim Zych]

That's true. A compilation problem can result in that non-obvious symptom.
Always use Option Explicit at the top of each module and compile the project
frequently. A good habit generally.

 

[salgud]

It may be thinking your macro is invalid. How do you have a space in the name
of your macro? I do not have XL2007, but all the macros I've seen need a name
like

CreateTR
or
Create_TR

Note that this may not be the case, and may instead be a feature that 2007
allows. I've just never seen it before.

Reply to all.
Thanks for the help!

The space in the macro name was a typing error I made when I posted. The
macro name was fine, or it never would have run in the first place.

Luckily, I had dinner with a programmer friend of mine last night. I told
him how the program ran (with bugs) on Tues, then couldn't be "found" on
Wed. He explained what happened. When I first ran the macro on Tue, it had
no errors and compiled and ran. I then made some changes I needed to make,
and got a compile error. I shut down the computer Tue night, and when I
booted up and tried to run in on Wed, I got the "macro not found" error. My
friend explained that when I ran the macro Tue and it ran, it was compiled
and put into memory. Then, when I shut down, it was removed from memory.
But when I booted up Wed, it couldn't compile and therefore was not loaded
in memory and couldn't be found. Weird, but he tells me quite normal. Makes
sense to me.