Userform and "ESC" - how to prevent?

**hannes001** · 02-02-2009, 10:28 AM

When user presses "ESC" key on an open userform, data he has changed in controls (simple textbox for example) gets resetted. Right? I'm seeing such behavior in Excel 2003. How about other versions?

I'd like to see some documentation about what exactly is going on, and how to prevent such unexpected feature (that forces data loss), but could not find.

I mean that i have no commandbutton with cancel=true. By default I want to keep the data user has entered/changed. Unfortunately this data reset finds place before any trappable event, or am i wrong?

I have not found any working way to capture keypresses on userform level. Userform has _KeyPress and other events, but those are not firing? If there is one, that would be helpful. Perhaps even on system level with some APIs? Altough that sounds way too complicated for a problem that should need only one little-known flag set. Perhaps there is such property somewhere?

--
Please pardon my language, this is my first public writing in english.

**mikerickson** · 02-02-2009, 10:30 AM

Setting the Cancel property of a CommandButton to True will cause that button's code to be run when the Escape key is pressed.

**hannes001** · 02-02-2009, 11:17 AM

Yes,
but unfortunately all changes get lost before that event fires. At least for me. Now, if i could capture data before it gets lost, or cancel that kind of action completely..

I think i could use a global array for all values i want to keep, and update this array on every _change or such event, but even such ugly workaround might get even uglier - this unfortunate data reset causes those events fire and i need to somehow determinate whether this event was caused by user input or "ESC".. I'm talking theoretically now, i don't even want to try this approach.

But thanks for taking your time. I'll try to patch together a simple example for illustration.

EDIT:
Now, tabula rasa method is always helpful. I'm sorry, I was little too quick before. Headache didn't help either. This resetting apparently does not apply to whole form as i said before, only to currently active input. That is not half as bad as before

I had too much code around that acted upon some input and mixed things up.

But unwanted behaviour is this nevertheless. I guess i could trap _KeyPress event (for every input..) and cancel "ESC" there. Or move focus. Suddenly it seems that it should be very well documented, because different controls (textbox vs checkbox) seem to act differently.. I try again.

**LordMarcus** · 02-03-2009, 01:34 AM

Hi,

I believe that the pressing the ESC key while the object in focus (i.e. an object that is used for inputing data) will trigger an UNDO function. Good examples are textboxes, the cells in an Excel sheet and the box where you type your reply here in ExcelForum.

I still also can't find decent documentation about this ESC Dilemma.

Anyway, I can help you trap the ESC Keypress. Suppose you have a Textbox, use the Keydown event:

Please Login or Register  to view this content.

Keypress event is not reliable when using VBA.

**Leith Ross** · 02-03-2009, 05:21 AM

Hello hannes001,

To do this is requires advanced level programming called Sub-Classing. This is a technique that allows you eavesdrop on messages being sent to a particular window and either trapping the message or passing it on. In this case the message of interest is IDCANCEL. This is in the wParam word sent along with the WM_COMMAND message to the TextBox.

This behavior is by design. The TextBox can operate in 2 modes: Single line or Multi-line. In single line mode the Enter key determines when the input is complete. In multi-line mode, we want to include the Enter key as the new line character. This is where the Escape key comes in. Pressing the Esc key in multi-line mode signals the end of input. In single line mode, the Esc key functions as Cancel operation. Deleting the current input and restoring the previous input, if any.

**hannes001** · 02-03-2009, 07:16 AM

First, thanks to LordMarcus. It was useful tip, i didn't know that you can change KeyCode there... Anyway this is a workaround i can deal with. Altough i might have dozens of such textboxes, some generated on runtime... I personally don't like lots of repeating code. At least it is simple and somewhat readable.

But,
Leith Ross, i'm pondering whether i dare to ask for a example? You make it sound like this technique requires pages and pages of complex API wrappers, several class modules and loads of arcane tricks.. In that case, let it be. For now.
I already have numerous classes, pages of spaghetti code and several bruises on my forehead.

Thanks for explanation though, now it sounds rather logical. What other controls might behave in such way? I'd be thankful for a recommendation for a book/webpage about VBA events and userforms where such rather little-documented-features, unexpected or not, are dissected.