• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2797
  • Last Modified:

Excel VBA - {ESC} key to close any open user forms

What is the code to close any open user forms with the {ESC} key?  Thanks.
0
AndresHernando
Asked:
AndresHernando
  • 3
  • 2
2 Solutions
 
AndresHernandoAuthor Commented:
Clarification: When I hit the {esc} key, I want it to run code that will close any open user forms.
0
 
Martin LissRetired ProgrammerCommented:
Private Sub Worksheet_Activate()
' Goes to procedure CloseUserForms if escape key hit
Application.OnKey "{ESC}", "CloseUserForms"
End Sub

Open in new window


Put this in a Module.

Sub CloseUserForms()
UserForm1.Hide
UserForm2.Hide
'etc
End Sub

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
The above assumes that you have one or more userforms opened modeless (Userform1.Show vbModeless) and that you press the escape key when a sheet has focus.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
jpaulinoCommented:
You can use this:

Sub CloseAll()
Dim f As UserForm

    For Each f In UserForms
       Unload f
    Next
End Sub

Open in new window

0
 
Martin LissRetired ProgrammerCommented:
This is better since a sheet does not have to be in focus. Note: use paulino's code to actually close the forms since it's more flexible.

Option Explicit
Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
Private Const VK_ESCAPE = &H1B
Private Sub Worksheet_Activate()
    Application.OnKey "{ESC}", ""
    Do
        DoEvents
        x = GetAsyncKeyState(VK_ESCAPE)
        If GetAsyncKeyState(VK_ESCAPE) <> 0 Then
            CloseUserForms
        End If
    Loop
End Sub

Open in new window

0
 
AndresHernandoAuthor Commented:
Thanks guys.  Both inputs are very helpful.  --Andres
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now