?
Solved

Excel VBA Userform Controls Question

Posted on 2009-12-17
8
Medium Priority
?
710 Views
Last Modified: 2012-05-08
I have created a userform in Excel VBA, and on that userform is a "Quit" button. In the event handler for the Quit button, I have coded for a message box to appear asking the user "Are you sure you want to quit?" How can I make it so that pressing the Escape key is equivalent to clicking on the Quit button? (When I press Escape, I want the message box to pop up)
0
Comment
Question by:kcooke2424
  • 3
  • 2
6 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26078207
Set the command button's Cancel property to True.

Kevin
0
 

Author Comment

by:kcooke2424
ID: 26078288
Is there a way to make Escape perform the same event handler routine as the Quit button?
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 26078330
Open the user form in the editor, select the "Quit" command button, press F4, locate the "Cancel" property and set it to True. This will invoke the Quit button when the user presses ESCAPE.

Kevin
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 45

Expert Comment

by:patrickab
ID: 26079336
kcooke2424,

You will most likely need to set it up as in the attached file - see code below.

Patrick
In UserForm1 VBA code pane:

Private Sub UserForm_Initialize()
Me.CommandButton1.Cancel = True
End Sub

Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

Open in new window

Userform-cancel-01.xls
0
 
LVL 45

Expert Comment

by:patrickab
ID: 26079366
kcooke2424,

I should have read your requirements more carefully. The code below allows the user to continue even after pressing the ESC button - if so required.

It's in the attached file.

Patrick
Private Sub UserForm_Initialize()
Me.CommandButton1.Cancel = True
End Sub

Private Sub CommandButton1_Click()
Dim Msg, Style, Title, Response
Msg = "Are you sure you want to quit?"    ' Define message.
Style = vbYesNo + vbCritical + vbDefaultButton2    ' Define buttons.
Title = "Quit?"    ' Define title.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
    Unload UserForm1
End If

End Sub

Open in new window

Userform-cancel-02.xls
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26405818
The answer to the question:

"How can I make it so that pressing the Escape key is equivalent to clicking on the Quit button?"

is:

"Open the user form in the editor, select the "Quit" command button, press F4, locate the "Cancel" property and set it to True. This will invoke the Quit button when the user presses ESCAPE."

The asker stated in the question that he already had written the event handler with the message box and so did not need an event handler written.

Kevin
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

829 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