OnKey disabled by Excel at times?

[danhattan]

I'm wondering if Excel ignores OnKey statements at times.

I used OnKey to jump to a sub when the Esc key is pressed. I want it to work
within the context of a macro displaying a form. However, if a form is
currently visible, pressing the Esc key does nothing. Once the form is gone,
and the macro stops running, then pressing the Esc key jumps to the sub I
want it to.

My conclusion is that if Excel is expecting the user to deal with the form,
then it ignores the OnKey commands. Is this true? If so, unfortunate, since
the idea is to close the form when the Esc key is pressed.

If anyone can shed some light on this, much appreciated. Thank you.

 

[Dave Peterson]

Do you have a "Cancel" button on that userform?

If you do, then change its .Cancel property to True.

And add a "get out of the userform" procedure if you don't have it:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
'and anything else you need after you leave the userform
End Sub

If you don't have a cancel button, then add one. Make it small so you can hide
it under an existing control. And make the .tabstop property false (as well as
making the .cancel property true).

===
And excel will ignore the .onkey stuff lots of times--each time you're editing a
cell for example.

 

[danhattan]

Sweet! Worked perfectly as I did have a Cancel button on the form. Already
had the procedure, too, it just wouldn't execute. But as soon as I made that
change, Esc did exactly what I wanted it to.

Researched it a bit to understand why, and what a great tool to understand.
Went further and found Default = True, and that was the other OnKey thing I
was going to use (for my OK button), and now don't have to.

Thank you, thank you, thank you!

Dan

 

[danhattan]

Hi Dave.

If I could, another question about the larger issue. While my OK/Cancel
needs are now met, does the presence of an active form block the function of
keystrokes set by the OnKey method? It occurred that on one form, Ctrl + 1
(through 4) would be extremely useful for our users who prefer to stay on the
keyboard. However, same thing. While a form is up, Ctrl + 1 does nothing, but
once the macro presenting a series of forms completes, Ctrl + 1 works just as
I want it to. Is there a way around that?

And again, many thanks for the first tip. Love it.

 

[Dave Peterson]

The .onkey isn't going to work when the userform has focus.

Maybe you could use some _Keypress event for each control?