VBA to Move cell pointer within range

[tree5ppm]

My dear fellows,

I need to enter data only in C,D,E columns.. so, while I fill data on C1 and press enter the cell pointer need to move to D1. After I entered data in D1 and press enter the cell pointer need to move E1. after entering data in E1 then pressing enter, the cell pointer need to move C2,. Like the same it continues.., (whether I'm entering data or not if the cell pointer on C,D it need to move right side and if the cell pointer on E while pressing enter the pointer should go down next row of C).

Kind regards

 

[Peter_SSs]

@HighAndWilder
Suggested change to you selection change code to alleviate what I mentioned earlier

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge = 1 And Target.Column = 6 Then Range("C" & Target.Row + 1).Select
End Sub

Also just wondering the purpose of the worksheet change code? Since the OP seems only concerned with action after the Enter key which, with your code, will always trigger the selection change code anyway.

 

[tree5ppm]

I was saying that if you implement the post 5 code then you would not be able to select the whole sheet by clicking at the top left intersection of the columns headings and row numbers. Nor would you be able to select any other range that included any column F cells.

Now I understood @Peter_SSs

In what way did post 5 code not work?
Did you do this?

That is after entering the code, did you select another worksheet and then return to the one

Yeah.. Now it works, I was testing with only workbook that was having 1 Sheet.
But It didn't revert back to original settings (down) as it was before while we close the workbook.

Did you 'reactivate' the worksheet?

There will be other ways to change the setting and then change it back again.

I can't test it on anything but 365.

Works fine in 365.

Yep, Now I understood each code line and how it works. Thank you so much for you both @HighAndWilder & @Peter_SSs

 

[HighAndWilder]

@HighAndWilder
Suggested change to you selection change code to alleviate what I mentioned earlier

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.CountLarge = 1 And Target.Column = 6 Then Range("C" & Target.Row + 1).Select
End Sub

Also just wondering the purpose of the worksheet change code? Since the OP seems only concerned with action after the Enter key which, with your code, will always trigger the selection change code anyway.

Now I understood @Peter_SSs




Yeah.. Now it works, I was testing with only workbook that was having 1 Sheet.
But It didn't revert back to original settings (down) as it was before while we close the workbook.


Yep, Now I understood each code line and how it works. Thank you so much for you both @HighAndWilder & @Peter_SSs

Can you post the code that you are now using. Thanks

 

[tree5ppm]

Can you post the code that you are now using. Thanks

Sure.. Here it is., I altered (combined code that got from internet) @Peter_SSs code to work as I needed. It works great. But the thing is it's not moving to next row if I didn't enter any data in E

Dim OldDirection As Long

Private Sub Workbook_Activate()
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

Private Sub Workbook_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

Private Sub Workbook_Open()
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

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

Private Sub Worksheet_Activate()
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

Private Sub Worksheet_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.CountLarge = 1 Then
    With Target
      Select Case .Column
        Case 5
          .Offset(1, -2).Select
      End Select
    End With
  End If

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Application.MoveAfterReturnDirection = xlDown
End Sub

With your code it's not allowing to select all the entered data.

PS: I added this code in the ThisWorkBook

 

[HighAndWilder]

Sure.. Here it is., I altered (combined code that got from internet) @Peter_SSs code to work as I needed. It works great. But the thing is it's not moving to next row if I didn't enter any data in E

Dim OldDirection As Long

Private Sub Workbook_Activate()
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

Private Sub Workbook_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

Private Sub Workbook_Open()
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

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

Private Sub Worksheet_Activate()
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

Private Sub Worksheet_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
OldDirection = Application.MoveAfterReturnDirection
Application.MoveAfterReturnDirection = xlToRight  ' here we say move right
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If Target.CountLarge = 1 Then
    With Target
      Select Case .Column
        Case 5
          .Offset(1, -2).Select
      End Select
    End With
  End If

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    Application.MoveAfterReturnDirection = xlDown
End Sub

With your code it's not allowing to select all the entered data.

PS: I added this code in the ThisWorkBook

Where is this code?

It will only move to the next row if you have the appropriate code in the Worksheet Code Module.

Use this code in the Worksheet Code Module.

Private Sub Worksheet_Activate()
  Application.MoveAfterReturnDirection = xlToRight
End Sub

Private Sub Worksheet_Deactivate()
    Application.MoveAfterReturnDirection = xlDown
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.CountLarge = 1 Then
    With Target
      Select Case .Column
        Case 6
          .Offset(1, -3).Select
      End Select
    End With
  End If
  
End Sub

And this code in the Workbook Code Module.

Private Sub Workbook_Open()

  ' Change the sheet name as appropriate.

  If ActiveSheet.Name = "Sheet1" Then
    Application.MoveAfterReturnDirection = xlToRight
  End If

End Sub

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

End Sub

 

[tree5ppm]

It will only move to the next row if you have the appropriate code in the Worksheet Code Module.

Use this code in the Worksheet Code Module.

This is exactly what I was looking for. Thank you so much for giving your valuable time for the perfect solution.

Kind regards

 

[Peter_SSs]

Some comments:

• There is no need for the Select Case structure when there is only one 'Case'
• If the target is column 6 and the code selects column C in the next row, that will (unnecessarily) trigger the SelectionChange code for a second time

So instead of the above code I would suggest this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  With Target
    If .CountLarge = 1 And .Column = 6 Then
      Application.EnableEvents = False
      .Offset(1, -3).Select
      Application.EnableEvents = True
    End If
  End With
End Sub

 

[tree5ppm]

instead of the above code I would suggest this

Thank you so much for refining the code.

 

[Peter_SSs]

You're welcome.