Shortcut Key Assigned to Imported Macro (i.e. in personal.xls) Does Not Work

[eBob.com]

I've spent all day trying to make a macro available to all of my Excel
files. The option I chose was to create a personal.xls file and put the
macro there. That makes it available. I can "Run" the macro using Tools >
Macro > Macros > Run. But when I setup a shortcut key for the macro the
shortcut key does nothing. I'd appreciate any help or pointers anyone can
offer.

Thanks, Bob

 

[Otto Moehrbach]

It works for me. Exactly what did you do to setup the shortcut key and what
key did you choose? HTH Otto

 

[Dave Peterson]

Maybe...

My guess is that you're opening a file in that macro and that your shortcut key
includes a shift key.

Remove the shift key from that shortcut combination and try it again.

Holding the shiftkey when you open a workbook stops the open macros from
running. It also confuses excel/vba into thinking it should stop.

 

[eBob.com]

Thank you Otto and Dave. I was about to respond to your notes when I
realized that the shortcut which wasn't working was in a spreadsheet other
than personal.xls. Now I don't really care whether there is a shortcut for
personal.xls but I thought it might be helpful to know whether a shortcut
worked there. So I went to personal.xls and looked to see if a shortcut was
setup and it was. Tried it and ... nothing. Went back, for some reason, to
verify that a shortcut was assigned and the little key block (following
Cntrl+") was now blank! I know how impossible that sounds but that is what
I saw. So I supplied the key, "t", and it worked, sort of. Now the macro
gets invoked but I never see a form which it invokes! The same thing
happens in another open Excel file. In both cases the shortcut key is "t",
i.e. Cntl+t.

In both cases, i.e. personal.xls and the other Excel file which I have open,
Tools > Macro > Macros... > highlight the macro I want to run > Run works!
What the shortcut key might be doing is running the first subroutine in the
module rather than the subroutine I assign the shortcut key to. I can't
play with it tonight but I will tomorrow and if that turns out to be the
case I can simply move the subroutine I care about to be the first one in
the module. Of course I will let you know what I learn.

Thanks for your interest, Bob

 

[eBob.com]

After a lot of research (i.e. trying things) here is what I have learned. I
had a VBA subroutine named DoIt in a module also named DoIt. A shortcut key
setup for the DoIt subroutine just would not work. Instead of invoking DoIt
(the subroutine) the shortcut key invoked another subroutine in the module.
I changed the module name to DoItMod and the problem seems to be solved.
Initially I associated this problem with the import and export of my macro
to a personal.xls file. But my recollection now is that at the same time I
decided that Module1 was not very descriptive and so changed the module name
to DoIt. I was not unaware of the fact that I also called my "main"
subroutine in the module DoIt but I have been in the general programming
game for a long, long time and have never before seen a case where software
failed to distinguish between a module name and subroutine name which happen
to be the same.

My research has probably not been exhaustive but I have burned enough time
on this.

For the record I am using Office XP.

For search engines:
shortcut key problem when module name is the same as subroutine name

Thanks again to Otto and Dave who showed an interest in the problem and
helping me.

Bob

 

[Dave Peterson]

Actually, this problem (a module and a procedure share the same name) comes up
in the newsgroup every so often. (You ain't the first!)

But it's really a difficult problem to diagnose from a distance.

 

[eBob.com]

It's a difficult problem for some of us to diagnose even when it is up close
and personal!

Bob

 

[Dave Peterson]

Difficult the first time, but it won't be so difficult the second time you see
it <vbg>!

It's a difficult problem for some of us to diagnose even when it is up close
and personal!

Bob

<<snipped>>