CODEWITHSUNDEEP
excel
Pedro Antonio Pérez Paterna
Pedro Antonio Pérez Paterna

Reputation: 15

Excel: Move to right and then move down

I'm doing an excel which the user will input in a better way their data.

I put an example of what that I want to achieve: Input data direction on the excel

So... The user starts inputting data at A2, then presses "Enter" button and the selected cell goes to B2. Once the user inputs data at B2 cell and presses enter again, the cursor moves to A3 and so on. That's the movement that I want to implement to my sheet on excel. A2 --> B2 --> A3 --> B3 --> A4 --> B4... each time the user presses "Enter" button.

I tried blocking all the other columns that aren't A and B column and with a macro use the following code:

'Private Sub Workbook_Open()
'    Application.MoveAfterReturnDirection = xlToRight
'End Sub

'Private Sub Workbook_BeforeClose(Cancel As Boolean)
'    Application.MoveAfterReturnDirection = xlDown
'End Sub

But when cursor reach to last active cell, if I press "Enter" button, then the cursor jumps to the first cell of the sheet.

Any ideas? Thanks!

Upvotes: 0

Views: 225

Answers (1)

cybernetic.nomad
cybernetic.nomad

Reputation: 6418

Keep your code changing the MoveAfterReturnDirection as is and add the following to the worksheet:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 2 Then Range("A" & Target.Row + 1).Select
End Sub

Alternatively, don't touch the user's preferences and use:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then Range("B" & Target.Row).Select
    If Target.Column = 2 Then Range("A" & Target.Row + 1).Select
End Sub

In the worksheet

Upvotes: 0

Related Questions