Close userform with Esc without control_KeyPress() command for each control

Question

In this answer, it's shown that Excel userforms can be closed with Esc by setting up a control_KeyPress() sub for each control that can take focus - Close userform with escape button

I've gotten this to work, but I have several menus, each with a number of controls. I tried putting this routine on just one button, but it's not always quick to tab/nav back to a specific button.

I'm looking for a way to make it so that I either

1. don't have to create control_KeyPress() subs for every control that can take focus
2. or, can achieve the same goal (closing the userform with esc) in a different way

Thanks!

Answer 1

My idea would be to create a command button on the form, set the cancel property to true AND set the width and height to 0. Add the following code to the not visible button.

Private Sub CommandButton1_Click()
    Hide
End Sub

Leave the visible property on true

Answer 2

Create a small button, e.g. 6 x 6 so that it can be visible but unobtrusively positioned in one of the corners of the form. Making it this small stops the caption from being displayed and just leaves a neat, small square.

Create a button, (e.g. BTXX), and set:

• Cancel = True so that pressing the escape key "presses" this button
• TabStop = False so that tabbing through the form does not stop on the button
• Caption = "Esc" as a reminder of what the button is for

In the onclick event use:

Private Sub BTXX_Click()
    Unload Me
End Sub

In this example the escape button is "hidden" top-left on the form by setting Top=0 and Left=0. (See images below).

Userform Properties

Answer 3

If you already have a button for closing the userform then you can simply set its cancel property to true. It is then triggered by pressing escape, remains clickable, keeps its accelerator and stays in the tab order. You can only have 1 button with Cancel=True and the property editor takes care of this automatically.