How to trigger code if Enter key is pressed in a column

Question

I'm trying to move to first cell of next row of column "A" in excel whenever enter key is pressed in Column "H". My code so far is below;

Private Sub move_to_next_row(KeyCode As Integer, Shift As Integer)  
    If KeyCode = 13 Then
        ActiveCell.Offset(1, -7).Activate
    End If
End Sub

score 2 · Answer 1 · answered Aug 02 '19 at 11:17

2

There is no KeyDown event or something similar for worksheets. You can only check if a cell in column H was changed and then move to the first column in the next row.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Me.Columns("H")) Is Nothing Then
        Me.Cells(Target.Row + 1, "A").Select
    End If
End Sub

answered Aug 02 '19 at 11:17

Pᴇʜ

56,719
10
49
73

What if column H contains formula? – LEO Aug 02 '19 at 11:46
@LEO The `Worksheet_Change` only triggers if a constant value in the column H is changed. It does not trigger if the result of a formula changes. – Pᴇʜ Aug 02 '19 at 11:49
1

There are lot of things that can go wrong but then it is beyond the scope of the question. You can only show the way but to an extent and you have done your part :) BTW [Interesting Read](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640) – Siddharth Rout Aug 02 '19 at 13:14

Siddharth Rout · Answer 2 · 2019-08-02T14:46:32.523

1

Not For Points

Another way in case you want to trap the "Enter" key in Col H (irrespective of whether user made a change or not in column H)

Credits:

@Tom for Application.OnKey
@AsUsual for Worksheet_SelectionChange

Place this in the worksheet code area

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim wsName As String

    wsName = ActiveSheet.Name
    If ActiveCell.Column = 8 Then Application.OnKey "{Enter}", _
                                  "'MoveCursor" & Chr(34) & wsName & Chr(34) & "'"
End Sub

Place this in the module.

Option Explicit

Sub MoveCursor(wsN As String)
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then GoTo CleanExit
    If ActiveSheet.Name <> wsN Then GoTo CleanExit

    Cells(ActiveCell.Row + 1, 1).Select

CleanExit:
    '<~~ Reset the key to avoid undesirable sideeffects!
    Application.OnKey "{Enter}"
End Sub

edited Aug 02 '19 at 14:46

answered Aug 02 '19 at 13:31

Siddharth Rout

147,039
17
206
250

Nice, but I think it still has a glitch when using multiple workbooks. Haven't tested it but I think if you are in column H of that workbook and change to another workbook it will still use the `OnKey` so you would have to add a `Workbook_Deactivate` to deactivate the enter there too I think? Same for other worksheets in the same workbook (if it should only affect one worksheet) – Pᴇʜ Aug 02 '19 at 14:27
@Pᴇʜ: Good Point. Try it now – Siddharth Rout Aug 02 '19 at 14:46
Nice, looks perfect. – Pᴇʜ Aug 05 '19 at 05:47

Tom · Answer 3 · 2019-08-02T12:29:39.610

0

In your ThisWorkbook Object place the following

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

And then run using F5

Then in a normal module place

Sub move_to_next_row()
    Dim SelectRng As Range

    On Error Resume Next
    If ActiveCell.Column = 8 Then
        Set SelectRng = ActiveCell.Offset(1, -7)
    Else
        If Application.MoveAfterReturn Then
            Select Case Application.MoveAfterReturnDirection
                Case xlToLeft
                    Set SelectRng = ActiveCell.Offset(0, -1)
                Case xlToRight
                    Set SelectRng = ActiveCell.Offset(0, 1)
                Case xlUp
                    Set SelectRng = ActiveCell.Offset(-1, 0)
                Case xlDown
                    Set SelectRng = ActiveCell.Offset(1, 0)
            End Select
        End If
    End If
    On Error GoTo 0

    If Not SelectRng Is Nothing Then
        SelectRng.Activate
    End If
End Sub

Whenever you press the enter key move_to_next_row will be called. If the ActiveCell is in column H it will move the ActiveCell to Column A

edited Aug 02 '19 at 12:29

answered Aug 02 '19 at 11:48

Tom

9,725
3
31
48

This will kill the normal behavior of the Enter key (in any other place than column H) – Pᴇʜ Aug 02 '19 at 12:04
@Pᴇʜ Whilst not a great way to achieve it the Enter key seems to still work fine in my test - Have tested in column H and other columns – Tom Aug 02 '19 at 12:06
Strange, because in my test after `Application.OnKey "~", "move_to_next_row"` Enter doesn't switch to the next cell anymore in my sheet (only column H jumps as expected). If I then run `Application.OnKey "~"` it works again. – Pᴇʜ Aug 02 '19 at 12:11
1

@Pᴇʜ Ah I see what you mean - have handled it in my update. I thought you meant it wasn't calculating – Tom Aug 02 '19 at 12:21
@SiddharthRout You can use `Application.OnKey "{ENTER}", "move_to_next_row"` to trap that one – Tom Aug 02 '19 at 12:58
@SiddharthRout yup agreed - was just intending to demonstrate another way of achieving it as Excel doesn't have a proper way of implementing this. – Tom Aug 02 '19 at 13:19

score 0 · Accepted Answer · answered Aug 02 '19 at 13:22

Try this (also gets triggered by the down arrow)

Option Explicit

Private col As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Target
        If .Column = 8 And col = 8 Then
            Cells(.Row, 1).Select
            col = 0
            Exit Sub
        End If
        col = .Column
    End With
End Sub

How to trigger code if Enter key is pressed in a column

4 Answers4