You can create a procedure that executes when you press a specific key or combination of keys. For example, you can change the built key combination of Ctrl+S for saving a workbook to display your own custom pop-up dialog box. To do this, you capture the OnKey event. If you specify a key combination that Excel already uses, your new definition overrides the Excel combination.

How To Enter An Excel Worksheet Function In VBA. Strategy #1: Use The Insert Function Dialog Box To Identify Excel Worksheet Functions And The Function Arguments Dialog Box To Obtain Argument Descriptions. Strategy #2: Copy The Syntax Of A Worksheet Formula. An Alternative To Strategy #2. The Excel CODE function allows you to easily obtain the numeric code for the provided character (1 character string). The syntax for the CODE function in VBA is: CODE( string ) Parameters. String A single character string. Example usage Excel. Below example of using the Excel CODE function: VBA. Below VBA equivalent of the Excel CODE. Using the Code from Excel Macro Examples. Here are the steps you need to follow to use the code from any of the examples: Open the Workbook in which you want to use the macro. Hold the ALT key and press F11. This opens the VB Editor. Right-click on any of the objects in the project explorer. Go to Insert – Module.

Unlike most other events, the OnKey event is not associated with a specific object. For this reason, you access this event you using the OnKey method that is associated with the

Application object.

Excel VBA Code VBA code can be defined as the code that inputs in the visual basic window to perform a set of instructions or actions in excel and provide results. With the help of VBA code, we can reduce the time to perform a repetitive task, there will not be required much human intervention except to run the program.

The OnKey method has two different parameters. You use the Key parameter to specify the key combination, which you express as a string consisting of the combined keys you capture. You represent standard keys, such as a and 5, by simply typing the character for the key. You specify nonstandard keys, such as Delete and Insert, by placing the key name in brackets, such as {DELETE} or {INSERT}.

You must use the Procedure parameter to indicate the name of the procedure to execute at the specified time. Remember to enclose the procedure name in quotes.

Because the OnKey event is not associated with a specific object, you can place your procedure containing the method for accessing the event in any code module. Keep in mind that if you place the OnKey method procedure in a standard code window, you need to run the corresponding macro before the OnKey event code activates. You can place the OnKey method within the Workbook_Open procedure so that it loads as the workbook opens. See a section 'Run a Procedure as a Workbook Opens' for more information.

EXECUTE A PROCEDURE WHEN YOU PRESS KEYS

EXECUTE A PROCEDURE WHEN YOU PRESS KEYS

AcceptLabelsInF AutoUpdateFreqO ChangeHistoryDiO ConflictResolutio 1 - xlU Datel904 False DisplayDrawingC -4104 EnableAutoReco True EnvelopeVisible False

'-n In the Projects window, double-click the ThisWorkbook object code.

■ The code module opens for the ThisWorkbook object.

L0 In the Workbook_Open procedure, type Application.OnKey 'As', 'CustomSave', replacing '~s' with a valid key combination string and 'CustomSave' with the procedure to run.

Note: See the section 'Run a Procedure as a Workbook Opens' for information on the Workbook_open procedure.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

When specifying keys that do not display a character, such as Delete or Down Arrow, you stipulate the name of the key within braces, such as {Delete} or {Down}. For some specific keys, Excel provides special characters to represent the key when you combine them with other characters:

If you want to assign a particular key combination back to its original meaning in Excel, you omit the Procedure parameter:

TYPE THIS:

Application.OnKey '+^{LEFT}'

RESULT:

The custom key combination assignment is removed and Excel executes the default command for that key combination, if one exists.

To use one of these special characters in your key combination, enclose the character in braces. For example, to specify a procedure to execute when you press the precent sign you type the following code.

TYPE THIS:

Application.OnKey '{%}', 'ExecutePercent'

RESULT:

Whenever the % key is pressed on the keyboard the ExecutePercent procedure executes.

□ Open the workbook in Excel.

■ The Workbook_Open subroutine activates the OnKey method and the specified procedure executes when you press the key combination.

Create a new subroutine with the same name as the procedure specified in step 2.

Note: See Chapter 3 for information on creating subroutines.

□ Type the VBA code to run when the subroutine executes.

0 Close Excel.

□ Open the workbook in Excel.

□ Press the custom key combination.

■ The Workbook_Open subroutine activates the OnKey method and the specified procedure executes when you press the key combination.

Continue reading here: Vba And Excel Object Model Quick Reference

Was this article helpful?

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

Syntax

expression.OnKey (Key, Procedure)

expression A variable that represents an Application object.

Parameters

Remarks

Insert Vba Code To Excel Workbook

The Key argument can specify any single key combined with Alt, Ctrl, or Shift, or any combination of these keys. 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 (for example: Enter or Tab), use the codes listed in the following table. Each code in the table represents one key on the keyboard.

You can also specify keys combined with Shift and/or Ctrl and/or Alt and/or Command. To specify a key combined with another key or keys, use the following table.

To assign a procedure to one of the special characters (+, ^, %, and so on), enclose the character in braces. For details, see the example.

Note

There is no way to currently detect the Command key in recent versions of Office VBA. Microsoft is aware of this and is looking into it.

Example

This example assigns InsertProc to the key sequence Ctrl+Plus Sign, and assigns SpecialPrintProc to the key sequence Shift+Ctrl+Right Arrow.

This example returns Shift+Ctrl+Right Arrow to its normal meaning.

This example disables the Shift+Ctrl+Right Arrow key sequence.

Insert Vba Code In Excel 2016

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.