Why am I missing the KeyPreview property on the Form?

[MuadDubby]

Hi

I'm using Excel 2003, with VBA 6.3. If I go into the VBA editor and add a new form, it does not have the KeyPreview property. Any ideas why?

Thx,

 

[fumei]

Because it is not a property in VBA?

Gerry
My paintings and sculpture

 

[PHV]

KeyPreview is a property of an Access.Form object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MuadDubby]

Thx for the input guys.

But that both sucks and makes no sense. Excel VBA has KeyPress, KeyDown and KeyUp events for the form, which suggest the form could react to keys being pressed. If so, how would it do it without the key preview (unless it only works with forms that have no controls on them, which would make even less sense ...)

 

[PHV]

it only works with forms that have no controls on them [!]or all controls are disabled[/!]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MuadDubby]

Wow. What a waste of an event. Unbelievable. I guess they really expect the programmer to handle an ESC key press (for example) by hanling it in every single control on the form ...

I can't really think of a better way. Incidentally, that's what I'm trying to do. I've got a form with several controls (about 4 or 5 dozen, divided into several tabs of a tab control), along with an Ok and Cancel button. If I want to let the user press Esc instead of clicking on Cancel, I nee dto handle it in the KeyPress function of all the controls ?!

 

[PHV]

Why not simply set the Cancel property of the CommandButton to True ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MuadDubby]

You learn something every day...

Thx PHV. That did it

And since you seem to have all the answers, how do I get Excel to do my dishes?

Thx again,

 

[fumei]

PH, I am missing something, and of course am very willing to learn. I have two commandbuttons on a userform. I can not set both of them to Cancel = True. One of them always = False. If I make one True, the other goes false automatically.

This seems to match the OP situation with an Ok and cancel button. How does setting one of them Cancel = True help with being able to use ESC to dump the form?

Gerry
My paintings and sculpture

 

[PHV]

Gerry, in an UserForm you may have only one Cancel CommandButton (for Esc key) and only one Default CommandButton (for Enter key).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

I think I misunderstood. Two command buttons. Making one with Cancel = True makes pressing the ESC key move focus to the commandbutton with Cancel = True.

I thought the point was to be able to use ESC to actually perform a cancel operation, an unload so to speak.

If I want to let the user press Esc instead of clicking on Cancel

I can not get it to do that. ESC moves the focus to the commandbutton with Cancel = True. But it does not fire that commandbutton - it just moves the focus.

I still have to click the button. There is no "instead".

What am I missing?

Gerry
My paintings and sculpture

 

[PHV]

ESC moves the focus to the commandbutton with Cancel = True. But it does not fire that commandbutton
The Click event procedure doesn't fire ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fumei]

No, it does not.

Gerry
My paintings and sculpture

 

[fumei]

Just to be explicit.

A userform with three textboxes (but I also tried with other controls, it does not matter), and two commandbuttons. One commandbutton has Cancel = False. The other has Cancel = True.

Focus is in a textbox. I press ESC. Focus jumps to the commandbutton with Cancel = True.

Stop. End of story. The Click event does NOT fire.

Gerry
My paintings and sculpture

 

[PHV]

Gerry, works for me:
Excel 2003SP2, MS Forms 2.0, WinXPpro SP2
A userform with two textboxes, one commandbutton with default=true, another commnadbutton with cancel=true.
Focus anywhere, Esc key fires the cancel click event.
Focus anywhere but the cancel button, Enter key fires the default click event.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[strongm]

Works fine here as well - and is the documented action

 

[MuadDubby]

Same here. As soon as I hit ESC, the Cancel button gets the focus and the OnClick event is fired for that button.

What version of Windows, Excel and VBA are you using?

One possibility - did you type the name of the function yourself, or did you select it from the dropdown list of possible functions? You might have mistyped something in the name. I've done this a few times, and mistyped a character, and the function is never performed.

To verify this, in the Form code, select your button name from the Object dropdown (above the code pane, to the left) and the OnClick event from the Procedure dropdown (above the code pane, to the right). If you typed the function name correctly, the editor will position you in your function. If not, it will create a new one for you, so just move your code there.

 

[fumei]

Bloody hell....I had moved the Unload Me from the Click event with the button with Cancel = True.

Doh. Sorry. yes, it works as stated.

Gerry
My paintings and sculpture

 

[MuadDubby]

Ah yes. The infamous chair-keyboard-interface problem

 

[fumei]

Yeah, and those darn Stupid (registered trademark) pills someone slipped me.

Gerry
My paintings and sculpture