?
Solved

How to know via code when a user presses the windows X' or the ESC key in a VBA apps

Posted on 2011-10-29
15
Medium Priority
?
355 Views
Last Modified: 2012-05-12
We are trying to determine how to capture in the VBA code (specifically in Outlook VBA) when a user clicks the "X" button of the UserForm window and also if the user presses the ESCAPE key on the keyboard.
0
Comment
Question by:rayluvs
  • 9
  • 5
15 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 37050772
The following page explains the QueryClose event's syntax:

http://msdn.microsoft.com/en-us/library/gg278635.aspx

Thus, to disallow users to close the form by hitting the 'x' button:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode <> 1 Then 
        Cancel = True
        MsgBox "The Close box has been disabled", vbExclamation, "No soup for you!"
    End If

End Sub

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37050821
That helped on when the user clicks on the "X" exit button of the form, but what about when the user presses the ESCape keyboard?
0
 

Author Comment

by:rayluvs
ID: 37050846
We have a UserForm that does a drop-down combobox window.

We have a Boolean variable named 'OptionNotSelected' that we want to set to TRUE when the user either press the ESCape keyboard or clicks on the "X" of the UserForm to exit.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 total points
ID: 37051001
You can use the KeyPress event for that:


Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    
    If KeyAscii = 27 Then MsgBox "Foo!"
    
End Sub

Open in new window



The Esc key's ANSI/ASCII value is 27.
0
 

Author Comment

by:rayluvs
ID: 37051033
doesn't work
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 37051046
Please show the exact code you are trying, and explain what you mean by "doesn't work".
0
 

Author Comment

by:rayluvs
ID: 37051115
I just copied your exact code.  Attached is an example in a an Excel (we have to do this for both an Excel and Outlook).  


TestEsc-Xbutton.xlsm
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 total points
ID: 37051123
Use the same events for your controls:


Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 If KeyAscii = 27 Then MsgBox "Foo!"
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 If KeyAscii = 27 Then MsgBox "Foo!"
End Sub

Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 If KeyAscii = 27 Then MsgBox "Foo!"
End Sub

Open in new window

0
 

Author Comment

by:rayluvs
ID: 37051154
Ok, you initial script was placing it in the UserForm, we have to place it in the field area not the form.

How about when clicking on the "X" corner ?

How doe we use your script:


   If CloseMode <> 1 Then
        Cancel = True
        MsgBox "The Close box has been disabled", vbExclamation, "No soup for you!"
    End If
0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 total points
ID: 37051209
Just add the code I gave you in http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27421900.html#a37050772 to your UserForm's code module.

The code I supplied there cancels the event by setting Cancel = True.

You do not have to do that, of course.
0
 

Author Comment

by:rayluvs
ID: 37051466
I placed it in the UsertForm1 and it didnt work..  Do i have to put it in the fields (TextBox)?
0
 

Author Comment

by:rayluvs
ID: 37051609
It did worked!

Thanx!!
0
 
LVL 84

Expert Comment

by:David Johnson, CD, MVP
ID: 37052524
escape won't work in the form itself but the close will as it is not in the text box where the 'textbox' has the focus all the escape key does is clear the box
0
 

Author Comment

by:rayluvs
ID: 37053326
Dont understand, I tried it and it works, ESC key when pressed is being captured.
0
 

Author Closing Comment

by:rayluvs
ID: 37091009
Thanx
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
Mailbox Corruption is a nightmare every Exchange DBA wishes he never has. Recovering from it can be super-hectic if not entirely futile. And though techniques like the New-MailboxRepairRequest cmdlet have been designed to help with fixing minor corr…
CodeTwo Sync for iCloud (http://www.codetwo.com/sync-for-icloud?sts=6554) automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question