Keypress events

While you work, Excel constantly monitors what you type. Because of this, you can set up a keystroke or a key combination to execute a procedure.

Here's an example that reassigns the PgDn and PgUp keys:

Sub

Setup_OnKey()

Application.OnKey "{PgDn}",

"PgDn_Sub"

Application.OnKey "{PgUp}",

"PgUp_Sub"

End

Sub

Sub

PgDn_Sub()

On Error Resume Next

If TypeName(ActiveSheet) =

"Worksheet" _

Then ActiveCell.Offset(1,

0).Activate

End

Sub

Sub

PgUp_Sub()

On Error Resume Next

If TypeName(ActiveSheet) =

"Worksheet" _

Then ActiveCell.Offset(-1

, 0).Activate

End

Sub

After setting up the OnKey events by executing the Setup_OnKey procedure, pressing PgDn moves you down one row. Pressing PgUp moves you up one row.

Notice that the key codes are enclosed in braces, not parentheses. For a complete list of keyboard codes, consult the Help system. Search for OnKey.

In this example, I use On Error Resume Next to ignore any errors that are generated. For example, if the active cell is in the first row, trying to move up one row causes an error that can safely be ignored. Also, notice that the procedures check to see which type of sheet is active. The routine reassigns the PgUp and PgDn keys only when a worksheet is the active sheet.

By executing the following routine, you cancel the OnKey events:

Sub Cancel_OnKey()

Application.OnKey

"{PgDn}"

Application.OnKey

"{PgUp}"

End Sub

Using an empty string as the second argument for the OnKey method does not cancel the OnKey event. Rather, it causes Excel to simply ignore the keystroke. For example, the following statement tells Excel to ignore Alt+F4. The percent sign represents the Alt key:

Although you can use the OnKey method to assign a shortcut key for executing a macro, you should use the Macro Options dialog box for this task. For more details, see Chapter 5.

+1 0

Responses

  • demet
    How use even keypress macro vba?
    8 months ago
  • valentin harju
    How to set keypress event in excel vba?
    6 months ago
  • anne
    How to use a keypress event in webpage vba?
    12 days ago

Post a comment