Run a macro in Excel 2010 using the enter key, whether the worksheet has changed or not when pressed in a certain range

Question

Short version of the question: How to make a macro program in Excel 2010 when a user presses a numeric key [Enter] in a given range of cells and does it change the spreadsheet since the last macro run ?

LONG-TERM VERSION: I have a macro in Excel 2010, and currently I have a button on the worksheet that the user clicks to launch it. The user enters 4 data cells (in A2: D2) and runs a macro to populate several tables on several sheets with data. The user can enter several hundred data sets in one session and currently leaves the mouse cursor over the button, enters data and clicks the mouse without moving it to click the button. I would like to simplify it by pressing the [Enter] key to run the macro so that they can leave their hand in the area of ​​the numeric key.

I found this question , but the answer was to use Worksheet_Change, and the user does not need an input key. I also found this question that was used on the [Enter] key, but also demanded that the worksheet change to be useful.

My user can use the same data several times, so I would like them to be able to press the [Enter] key several times. Currently, the macro ends by selecting A2, the first data cell, so that the user can enter new data or click it again to start it again. I want it to run from anywhere in the range A2: D2, selecting A2 when it is completed (as it is now), and if [Enter] is pressed, when any other cell is selected or a cell is selected on any other sheet, I want he moved around the cell, as is currently happening, without running a macro.

Useful names / numbers / things:

• The data range will always be in Sheet 1, cells A2: D2.
• The macro that will be run is called "InsertIntoTables ()" and does not accept any parameters.
• No other application level macros or events are currently used.
• The array is already processing empty or incorrectly filled cells in my range.

If there is anything else for the answer, I am happy to provide it.

Answer 1

This is just to get you started. Say by clicking the mouse button using the InsertIntoTables() buttons. I assume InsertIntoTables() is in the standard module . (this makes it easier to call from another sub)

First start:

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

After that:

• touching the ENTER key will have the same effect as clicking the button
• normal ENTER key will not be affected

To restore the ENTER numeric keypad, run this:

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

What you must do:

The numeric keypad ENTER will call your subtitle no matter which worksheet is active. You must add logic to determine which sheet is active and take appropriate action. You must choose which cell you want when your subcomponent is complete. You must remember to start recovery before exiting.

EDIT # 1:

I have two sheets; Sheet1 and Sheet2.
Sheet1 is the sheet in which I want to use the "special" ENTER key.

I placed the following in a standard module :

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

I placed the following in the area of ​​the page <1> :

 Private Sub Worksheet_Activate() Call NumericEnter End Sub Private Sub Worksheet_Deactivate() Call ClearEnter End Sub 

I placed the following in the code area of ​​the book :

 Private Sub Workbook_BeforeClose(Cancel As Boolean) Call ClearEnter End Sub Private Sub Workbook_Open() Sheets("Sheet2").Activate Sheets("Sheet1").Activate End Sub 

The purpose of the Workbook_Open() macro is to ensure that we start with the active Sheet1 and the active ENTER key.

EDIT # 2:

Use this instead:

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

Link:

Reply to Tim