Search code examples
vbaexcelkeypress

VBA: application.onkey not working

I'm on VBA with an apparently simple code, trying to create an event triggered by pressing Enter. So here is my code in the workbook: 

Option Explicit

Private Sub Workbook_Open()

    Application.OnKey "", "pressEnter"

End Sub

And what should be happen in the module:

Public Sub pressEnter()

    ThisWorkbook.Worksheets(1).Cells(1, 1).Value = 20

End Sub

However, when I press Enter/Return key, nothing happens. Just as with a normal Enter, it selects the cell inferior to the active one. I tried with other keys, such as ^+b or ^+a, and I have the same problem.

Does someone see any solution ?

Solution

  • You need to specify a key:

    • For Enter on the numeric keypad use Application.OnKey "{ENTER}", "pressEnter"
    • For the normal Enter use Application.OnKey "~", "pressEnter"

    see Application.OnKey Method (Excel)

    Be aware that other keys like ^+b means ctrl+shift+b. Make sure that Public Sub pressEnter() is in a module and not in ThisWorkbook. If it doesn't work in Workbook_Open() try Workbook_Activate() instead.