1

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!

Community
  • 1
  • 1
DukeSilver
  • 458
  • 1
  • 6
  • 22

3 Answers3

3

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

enter image description here

Storax
  • 11,158
  • 3
  • 16
  • 33
  • 1
    Interesting. But to do this, you'd have to click that button to cancel out, right? I'm trying to avoid having to use the mouse. – DukeSilver Apr 16 '18 at 18:04
  • 1
    No, because I set the cancel property to true pressing the Escape key will run the code behind the button. Or in other words pressing Escape is like clicking on the button. – Storax Apr 16 '18 at 18:06
  • Ah, I didn't know it worked that way, but it sure does. Thanks! – DukeSilver Apr 16 '18 at 18:49
  • As an alternative to making the button height/width = 0: make the userform bigger, drag the button somewhere out of normal view, and then drag the userform size back to normal. This would hide the button from view while letting you find it if you need to for whatever reason. – DukeSilver Apr 16 '18 at 18:55
0

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

Yonah
  • 15
  • 7
0

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.

Yonah
  • 15
  • 7