5

I am trying to help another user in this forum (reference). The goal is to use the Enter key on the numeric keypad to trigger a macro rather than use the Worksheet_Change event macro for the same purpose.

Before anything is run, both Enter keys work identically. You type stuff in, touch either key, the material gets placed in the cell and Selection moves downward. If you just pump either key, Selection just moves downward.

In a standard module I put:

Sub NumericEnter()
    Application.OnKey "{ENTER}", "InsertIntoTables"
End Sub

and

Sub InsertIntoTables()
    MsgBox "Inserting"
End Sub

After I run NumericEnter(), the Enter key on the numeric keypad calls the proper macro each time it is touched. The normal Enter key is not affected.

I then tried to restore the numeric Enter key by running:

Sub ClearEnter()
    Application.OnKey "{ENTER}", ""
End Sub

The numeric Enter key partially reverts to its original behavior. It no longer calls the macro; it allows data entry into cell, but it does not move Selection at all! (the regular enter key still behaves normally)

I have no idea of what I have missed.

What I have looked at:

  • The normal enter key, the ARROW keys, and the TAB key work just fine at changing Selection
  • Mouse clicking works fine at changing Selection
  • the values of Application.MoveAfterReturn and Application.MoveAfterReturnDirection look fine as well.
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Have you tried mapping to `vbNullString` instead of `""`? Might not work, but maybe receiving a null string pointer can hint `OnKey` that we're trying to *remove* a macro (as opposed to *remap*) - just thinking out loud, haven't tried it – Mathieu Guindon Jan 31 '18 at 22:39
  • 1
    @Mat'sMug Thanks for the suggestion............sadly, the behavior is the same. – Gary's Student Jan 31 '18 at 22:43
  • Interestingly, it looks like [this post](https://www.rondebruin.nl/win/s4/win012.htm) suggests you're doing everything right. The plot thickens... – Mathieu Guindon Jan 31 '18 at 22:54

1 Answers1

5

Using just 

Application.OnKey "{ENTER}"

with no second argument seems to clear the macro mapping and return the default key action (Excel 2013)

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-onkey-method-excel

expression.OnKey( Key , Procedure )

The description for the Procedure parameter has this:

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.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125