0

I am new to VBA. I have made a look-up function using vba and it works so fine. Here is what I wanted, I want to call my subroutine (that contains the look-up) when the Enter key is pressed. How am I going to do this? How does an event like this in VBA work?

Community
  • 1
  • 1
Kentot
  • 512
  • 5
  • 10
  • 26
  • I think you mean - Worksheet_Change http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change – Sathish Kothandam Dec 26 '13 at 07:04
  • I would add a word of caution. Anytime you reuse a key that other purposes, that key will no longer function as the user expects. [Enter] has many functions in Excel and none of those will continue to work with this functionality added. If you've taken that into consideration, then disregard. Just make sure the user is made aware of the change in functionality. – guitarthrower Dec 26 '13 at 17:01

2 Answers2

0

Use Application.OnKey with a Workbook_Open event. This way, every time your workbook is loaded, you can call your macro on pressing Enter. See below:

Private Sub Worksheet_Open(ByVal Target As Range)

    Application.OnKey "{RETURN}", "MyLookUp"

End Sub

Make sure you paste it in the ThisWorkbook's code. Let us know if this helps.

WGS
  • 13,969
  • 4
  • 48
  • 51
0

workbook> in the Open (and window activate) Subroutine :

Application.OnKey "{RETURN}", "Sub_Enter"
Application.OnKey "{ENTER}", "Sub_Enter"

the is a difference between numpad Enter and the other Enter.

and on workbook close (and change_window...) : (to deactivate it)

Application.OnKey "~"
Application.OnKey "{ENTER}"

You will also need a custom bit of code to know if the content of a cell has changed + ENTER. (in that case your macro Enter will have to ignore and exit sub)...

Patrick Lepelletier
  • 1,596
  • 2
  • 17
  • 24