Stopping Macro with the ESCAPE Key in Excel

[theDabster]

Folks,
If this has been previously answered, please send where I can read your reply.

I am just starting to learn how to write macros in Excel. I would like to get the code that stops and exits the macro when the ESCAPE Key is pressed.

I have looked through the 2 books that I have as well as the help files. I'm either not "asking" the right question or what I'm looking for isn't included.

Any help will be greatly appreciated and thanks in advance.

Dave

 

[BrainB]

Why do you want to do that?
To stop a macro you can use 'End' or 'Stop' to debug.

Regards
BrianB
======================================== Regards
BrianB
================================

 

[GeekGirlau]

You can also press Ctrl-Break to stop a macro.

 

[theDabster]

Let me try asking again.

I'm trying to write a VBA macro for Excel to help the guys at work fill in the work schedule for the employees. The macro goes down from cell to cell and pops up an InputBox seeking a starting time, an ending time and either zero lunch time or 1 hour.

After inputting the time for Monday the macro moves to the next column and repeats itself, then moves over one more column and repeats etc, etc.

What I'm wanting is the code to exit the macro should the person want to quit inputting before cycling through all six days (Mon thru Sat), or if he or she makes a mistake and wants to start over. I want them to press the ESC key to stop and exit the subroutine.

What is the code to allow me to write this? In my reading, I think it is based on the OnError command, but I'm just not getting it correctly.

Hope that makes my request more clear.

Dave

 

[JustinEzequiel]

you might want to check out the 'OnKey' method of the Application object

from Excel help:

OnKey Method

Runs a specified procedure when a particular key or key combination is pressed.

Syntax

expression.OnKey(Key, Procedure)

expression Required. An expression that returns an Application object.

Key Required String. A string indicating the key to be pressed.

Procedure Optional Variant. A string indicating the name of the procedure to be run. If Procedure is "" (empty text), nothing happens when Key is pressed. This form of OnKey changes the normal result of keystrokes in Microsoft Excel. If Procedure is omitted, Key reverts to its normal result in Microsoft Excel, and any special key assignments made with previous OnKey methods are cleared.

Remarks

The Key argument can specify any single key; any key combined with ALT, CTRL, or SHIFT, or any combination of these keys (in Windows); or COMMAND, CTRL, OPTION, or SHIFT, or any combination of these keys (on the Macintosh). Each key is represented by one or more characters, such as "a" for the character a, or "{ENTER}" for the ENTER key.

To specify characters that aren't displayed when you press the corresponding key (ENTER or TAB, for example), use the codes listed in the following table. Each code in the table represents one key on the keyboard.

Key Code
BACKSPACE {BACKSPACE} or {BS}
BREAK {BREAK}
CAPS LOCK {CAPSLOCK}
CLEAR {CLEAR}
DELETE or DEL {DELETE} or {DEL}
DOWN ARROW {DOWN}
END {END}
ENTER (numeric keypad) {ENTER}
ENTER ~ (tilde)
ESC {ESCAPE} or {ESC}
HELP {HELP}
HOME {HOME}
INS {INSERT}
LEFT ARROW {LEFT}
NUM LOCK {NUMLOCK}
PAGE DOWN {PGDN}
PAGE UP {PGUP}
RETURN {RETURN}
RIGHT ARROW {RIGHT}
SCROLL LOCK {SCROLLLOCK}
TAB {TAB}
UP ARROW {UP}
F1 through F15 {F1} through {F15}
In Windows, you can also specify keys combined with SHIFT and/or CTRL and/or ALT. On the Macintosh, you can also specify keys combined with SHIFT and/or CTRL and/or OPTION and/or COMMAND. To specify a key combined with another key or keys, use the following table.

To combine keys with Precede the key code by
SHIFT + (plus sign)
CTRL ^ (caret)
ALT or OPTION % (percent sign)
COMMAND * (asterisk)
To assign a procedure to one of the special characters (+, ^, %, and so on), enclose the character in braces. For details, see the example.

 

[rmikesmith]

Dave,

I did some experimenting and if you use Excel's InputBox method in your VBA routine, then pressing ESC will assign "False" to your variable (assuming the Type parameter is set to string, which you would use in this case). Check for this value immediately after the call to InputBox. Here's an example:

Inp = Application.InputBox("Starting Time:","Input", , , , , , 2)
If Inp = "False" Then Exit Sub

VBA's InputBox returns an empty string when ESC is pressed or Cancel clicked. But a user could also have mistakenly pressed OK without entering a value, also resulting in an empty string.

BTW, the complete syntax for Excel's InputBox is:

object.InputBox(prompt, title, default, left, top, helpfile, context, type)

with prompt being the only required parameter. Values for the Type parameter are
0 Formula
1 Number
2 String
4 Logical
8 Cell reference (as range object)
16 Error value (e.g. #NA)
64 Array

Hope this helps
M. Smith

 

[theDabster]

Folks,
Thanks for the advice. I'll plow through your posts and see which one works best. I appreciate your time in responding.

Dave

 

Hi Dave,

Actually from memory, if a user selects Cancel for an input box prompt, the value stored is "". So you can use something like this:

varStart = InputBox("Enter the starting time&quot

If varStart = "" then
Exit Sub

Else
.... (normal process)
End If