excel macro Enter

Question

I'm trying to record this macro: Pressing Enter key, the first cell of the next row fills with autoincrement value =ROW()-7 and the next cell gets the focus.

How can I use Enter key for the macro? I also have problems with ranges of the macro, any tip or solution?

Do you want the normal **ENTER** key or the **ENTER** key on the numeric keypad?? — Gary's Student, May 23 '18 at 15:49
I think this is an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). What are you really trying to do? You usually don't want to actually mimic keypresses. There's a way to select that next cell with VBA. What code do you have now? Generally, it could look like `Sub mySub() // Range("A1").Formula = "=ROW()-7" // Range("A1").Offset(1,0).Activate` ...even though you should [avoid using `.Select/.Activate`](https://stackoverflow.com/questions/10714251/). — BruceWayne, May 23 '18 at 15:57
@BruceWayne thanks for answering. I'm trying to start the macro once ENTER key is pressed, and then jump to the first cell of the next row, write and focus the row on its right side. But do this no matter in what row am I. Did I explain ok? I'm sorry, if not. Ask more if its needed for the explanation please — Juan, May 23 '18 at 16:08
So, when the user has your workbook/spreadsheet open, you want the macro to fire any time they press `ENTER`? **Do not do that**. What's your goal with the macro, to enter the `=ROW()-7` formula in a current cell, and after it's entered, go down one row, over one column? For example, you want `=ROW()-7` in `A1`, and after that's entered, have the active cell be `B2`? — BruceWayne, May 23 '18 at 17:01
@BruceWayne the goal is when I press ENTER, the first cell of the next row fills with =ROW()-7 formula and the cell on its right gets the focus. Thanks for answering — Juan, May 24 '18 at 07:47

score 0 · Accepted Answer · answered May 23 '18 at 16:58

0

When I press the Enter key while in a cell it causes excel to select the next cell. You could use the Worksheet_SelectionChange() event to capture that and perform the action:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Toggle this off to avoid endless loops
    Application.EnableEvents = False

    'Add the date
    Target.EntireRow.Cells(1, 1).Value = Now - 7

    'Select the "Next Cell"?
    Target.EntireRow.Cells(1,2).Select

    'Toggle back on so this event fires again
    Application.EnableEvents = True

End Sub

Put that in the code page, in VBE, for whatever sheet you want this to happen.

Even though we aren't capturing the Enter key push directly, this does exactly what you described without having to perform any keybinding.

answered May 23 '18 at 16:58

JNevill

46,980
4
38
63

I paste this code in ThisWorkbook(code) and it does nothing. Do I have to do anything else? Thanks – Juan May 24 '18 at 07:44
You'll need to put it in the code for the particular worksheet that you want it to work on. If you want this to work on ALL worksheets in the workbook, then a different event can be used in `ThisWorkbook` code with a few tweaks. Let me know and I can write that up real quick. – JNevill May 24 '18 at 13:31
@Juan if this solved it for you, please flag as The Answer by clicking the check mark left of the post. – BruceWayne May 27 '18 at 00:02

score 0 · Answer 2 · answered May 23 '18 at 16:59

Use OnKey. Do one of the following:

1) Place this in the ThisWorkbook module (this will take over the enter key when you open the workbook and put it back to normal when you close):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "~"
End Sub

Private Sub Workbook_Open()
    Application.OnKey "~", "AutoIncrement"
End Sub

--OR--

2) Add this to a module and call CatchEnter it to toggle the behavior:

Public blnCatchEnter As Boolean

Public Sub CatchEnter()
    If blnCatchEnter Then
        Application.OnKey "~"
        blnCatchEnter = False
    Else
        Application.OnKey "~", "AutoIncrement"
        blnCatchEnter = True
    End If
End Sub

Next, add this macro to a module:

Public Sub AutoIncrement()
    If TypeName(Selection) = "Range" Then
        Dim lngRow As Long

        lngRow = Selection.Row + 1

        Range("A" & lngRow).Formula = "=ROW()-7"
        Range("B" & lngRow).Select
    End If
End Sub

Although, I agree with @BruceWayne that you may not want to take over the Enter key this way. Assigning a shortcut to AutoIncrement will be safer.

Thanks for your answer, but I'm sorry I'm noob in excel. I paste the first option in ThisWorkbook (code) page and it does nothing. Should I do anything else? And if I choose the second option, can I paste both in the same Module page? Thanks — Juan, May 24 '18 at 07:58
For the first solution, you need to save and reopen the workbook. For the second, put it into a new module (Insert > Module). — jblood94, May 31 '18 at 19:26

excel macro Enter

2 Answers2