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

  • Thread starter Thread starter eBob.com
  • Start date Start date
E

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
 
It works for me. Exactly what did you do to setup the shortcut key and what
key did you choose? HTH Otto
 
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.
 
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
 
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
 
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.
 
It's a difficult problem for some of us to diagnose even when it is up close
and personal! :)

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

eBob.com said:
It's a difficult problem for some of us to diagnose even when it is up close
and personal! :)

Bob
Click to expand...
<<snipped>>
 
Back
Top