2

The short version of the question: How can I make a macro run in Excel 2010 when the user hits the numeric [Enter] key within a given range of cells, and whether or not they've changed the spreadsheet since the last running of the macro?

LONGER VERSION: I have a macro in Excel 2010, and I currently have a button on the sheet that the user presses to run it. The user enters 4 cells of data (in A2:D2) and runs the macro to populate several tables on several sheets with the data. The user may enter several hundred sets of data in a single sitting, and currently leaves the mouse hover over the button, enters the data, and clicks the mouse without moving it to hit the button. I'd like to simplify it by having a press of the numeric [Enter] key run the macro so they can leave their hand on the numeric key area.

I've found this question, but the answer was to use Worksheet_Change and the user didn't need the Enter Key. I also found this question, which used on the [Enter] key, but also required the worksheet to change to be of use.

My user may use the same data multiple times, so I'd like them to be able to press the [Enter] key multiple times. Currently the macro finishes by selecting A2, the first cell with data, so the user can enter new data or press it again to run it again. I want it to run from anywhere in the range of A2:D2, selecting A2 when complete (as it does now), and if [Enter] is pressed while any other cell is selected, or a cell on any other sheet is selected, I want it to move down a cell as it currently does, without running any macro.

Useful names/numbers/things:

  • Data range will always be in Sheet 1, cells A2:D2.
  • Macro to be run is named "InsertIntoTables()" and takes no parameters.
  • Currently no other application-wide macros or events are being used.
  • The macro already handles empty or improperly-filed cells in my range.

If there is anything else necessary to provide an answer, I'm happy to supply it.

Mister B
  • 125
  • 1
  • 5
  • Have you tried an **OnKey** macro?? – Gary's Student Jan 30 '18 at 01:15
  • Why must the key be the numeric enter? Why not a function key? Have you considered using a shortcut key which you can set up in seconds? (Developer tab > Macros > Options) – Variatus Jan 30 '18 at 01:30
  • @Variatus The idea behind using the numeric [Enter] key is that they will be entering only numerical data, and the users prefer the numeric keypad, so if they can use a single, intuitive keypress in that area, it makes sense. Having to hit something such as [F2] or [Alt + K] breaks it up just as much as clicking the mouse. – Mister B Jan 30 '18 at 16:12
  • @Gary's Student is showing you to do exactly what you want. I take it upon myself to tell you that your sense about that solution is correct: it is convoluted. That is because you reject the way Excel is designed to handle your requirement. You should use the `Worksheet_Change` event to run your existing procedure every time any one of the 4 cells is modified. Your macro should check if all entries are present and update your tables only if they are. No need for pressing any key at all. No running the macro "in error" or forgetting to run it or running it on the wrong worksheet. – Variatus Jan 31 '18 at 00:43
  • @Variatus I agree with your assessment. It is much easier to use the Change Event to run the code. Some Designers, however, want to give the user the opportunity to examine the entered values and visually verify them before clicking a button or touching the `Enter` key. That is why many web apps rely on a `Submit button` to complete transactions. – Gary's Student Jan 31 '18 at 00:53
  • @Gary's Student OP is interested in speed. Therefore the examination of entries must be carried out by whatever code is dealing with them. This is all the more true in this case since all entries are numbers. – Variatus Jan 31 '18 at 01:31
  • @Variatus Your idea to run the code whenever the four cells become filled is a good one, but the code has no way to distinguish what values should be, aside from "yes, that's a number." As such if a user fumbles and types "356" instead of "36" on the final number, it will have big ramifications that are not easy to fix in the sheet, but the code will already have run. – Mister B Jan 31 '18 at 19:21

1 Answers1

2

This is just to get you started. Say clicking the button with the mouse runs InsertIntoTables(). I am assuming that InsertIntoTables() is in a standard module. (this makes it easy to call from another sub)

First run:

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

After this has been run:

  • touching the numeric keypad ENTER key will have the same effect as mouse-clickng the button
  • the normal ENTER key will not be affected

To restore the numeric keyboard ENTER key, run this:

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

What you must complete:

The numeric keypad ENTER key will call your sub no matter which worksheet is active. YOU must add the logic to detect which sheet is active and take appropriate actions. You must select which ever cell you want when your sub completes. You must remember to run the restore sub before quitting.

EDIT#1:

I have two worksheets; 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 Sheet1 code area:

Private Sub Worksheet_Activate()
    Call NumericEnter
End Sub

Private Sub Worksheet_Deactivate()
    Call ClearEnter
End Sub

I placed the following in the workbook code area:

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 insure we start with Sheet1 active and the "special" ENTER key active.

EDIT#2:

Use this instead:

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

Reference:

Tims answer

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • As I will not be the one using the workbook, I'm trying to remove steps for my users. Is there a way to accomplish this without having to run either of the OnKey subs manually, and such that {Enter} will still behave normally elsewhere? – Mister B Jan 30 '18 at 16:16
  • You can accomplish this with a pair of **worksheet Event macros**. Use the `Activate Event` macro to call `NumericEnter()` and use the `Deactivate Event` macro to call `ClearEnter()` – Gary's Student Jan 30 '18 at 16:23
  • Can you expand that into a full answer? It might work, but I don't entirely know how to implement that. If you can expand and it works, I'd be happy to mark that as correct. – Mister B Jan 30 '18 at 17:16
  • @MisterB See my **EDIT#1** – Gary's Student Jan 30 '18 at 18:12
  • This is SO CLOSE! While on the sheet, it does everything I need it to. I adapted my macro to handle checking a few extra things so it works well. The problem is that if I go to any other sheet, the numeric [Enter] key does nothing. it doesn't run the macro, but it also doesn't move the selection like it typically would (Note: closing the workbook and opening another one returns it to its normal purpose, as expected). Any way to address this? – Mister B Jan 31 '18 at 19:25
  • @MisterB I understand the problem, but I have no immediate solution. At least I can verify the problem you are seeing. If I can't solve this I will open my own question on this forum, or you may do so. – Gary's Student Jan 31 '18 at 19:37
  • I appreciate your work so far. If you do find a solution, please do let me know. I would guess, from an uneducated perspective, that it has to do with assigning "" to that key on exiting the sheet, but I don't know the workaround. I'm giving you the tick because the core element of my question was addressed, and on a single-sheet scenario this would be flawless. The remaining quirk is another matter. – Mister B Jan 31 '18 at 21:42
  • @MisterB We have a solution. See my **EDIT#2** – Gary's Student Jan 31 '18 at 23:57
  • That solves it completely. Thanks for taking the extra steps! – Mister B Feb 01 '18 at 00:17
  • @MisterB I have already thanked Tim Williams for helping us out! – Gary's Student Feb 01 '18 at 00:19