MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

VBA Key stroke capture

  Asked By: Gail    Date: Dec 10    Category: MS Office    Views: 5716

How about a hint at capturing key strokes with a macro. I'm sure a
key stroke is an events and can fire a subroutine. On-Line Help
seems to tell what I want to do, but I can't quite decode these

HELP:" Runs a specified procedure when a particular key or key
combination is pressed. " [[yea, yea... right, just what I want.
Just like an 'interrupt' in the old days, eh? Now called 'events,
right' ]]

HELP:" expression.OnKey(Key, Procedure) " [[ O K...]]
expression Required. An expression that returns an Application
object. "

Yikes! I choke on this "expression...returns an Application
object.". I sort'a get objects and methods, but the concept fades in
and out. My brain thinks either in interrupts or these terms:

A$=Inkey$ [ check for key-stroke in the kbd buffer every time you
pass this line of code ]
IF A$ = "L" GoSub whatever.
continue with normal operation upon return...

I already use check boxes and cell selection changes to fire routines
just fine...

However, it appears that this may preclude typing into a cell when
HELP:" If Procedure is omitted, Key reverts to its normal result in
Microsoft Excel, and any special key assignments made with previous
OnKey methods are cleared. "

Therefore, perhaps I want to use 'non-printing' keys (maybe F-Keys),
but stay away from "Global keys" also found in the online help...Help
does, sometimes.



3 Answers Found

Answer #1    Answered By: Topaz Ramirez     Answered On: Dec 10

I got a simple version to work and think I see how it operates... Now
to figure out why I get the 'external' error in my live application.
I suspect it has to do with the location of the subs (module vs.
sheet, vs. ThisWorkbook). Certain I don't understand these places

Answer #2    Answered By: Angie Bennett     Answered On: Dec 10

When you're ready to share code, please do. For Word, the only option is
to build a class module and leave the module in the Word Startup folder.
I'm curious as to whether Excel uses a different facility for the same

Answer #3    Answered By: Ray Lawrence     Answered On: Dec 10

Just the code probably won't help. I am reading the Microsoft VBA
tutorials on-line  (Very well written--a.k.a. beware of John Doe's,
well meaning, but poor on-line explanations) and learning about where
code can be to execute from elsewhere. The source of some of the
problem (Other than my ignorance) is that the start-point code came
from elsewhere and it has code in Sheet1, ThisWorkbook and
Module1...so things like Private/Public calssification of subs &
fcn's and when something has to be in the same module as something
else is a factor as well. I got the OnKey to work, but it keeps
cancelling itself, so I'm getting close.

It always helps to learn something about the overall scheme of things
instead of jumping right  in, but I got pretty far. Now that I have
much of the code running, reading the tutorial will make more sense.
I usualluy get stuck in explanations like; "select the X option in
the Y bar." to which I can only say:" What the bleep is the Y bar?"

Didn't find what you were looking for? Find more on VBA Key stroke capture Or get search suggestion and latest updates.