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]

Try this event procedure. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    With Target
      Select Case .Column
        Case 3, 4
          .Offset(, 1).Select
        Case 5
          .Offset(1, -2).Select
      End Select
    End With
  End If
End Sub

 

[tree5ppm]

Try this event procedure. To implement ..

Hi @Peter_SSs Thank you so much for helping on my request.
Yes it works great. Only one thing needed. It works only while we enter the data, But I would like to move whether the data entered or not.

Kind regards

 

[tree5ppm]

And another thing, While I select all the data and Delete it shows error like this,

 

[HighAndWilder]

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

Try this.

It saves the existing setting for the direction that a Enter press takes the active cell and then changes it back again when the
worksheet is deactivated. You need to deactivate and then activate the worksheet to do this.

It works whether you change the cell value or not.

Do you want to enter anything into column F?

Option Explicit

Dim lngDirection As Long

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

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Count > 1 Then
    Exit Sub
  End If

  If Not Intersect(Target, Range("E:E")) Is Nothing Then
  
    Range("C" & Target.Row + 1).Select
  
  End If

End Sub

Private Sub Worksheet_Deactivate()
  
  Application.MoveAfterReturnDirection = lngDirection

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Not Intersect(Target, Range("F:F")) Is Nothing Then

    Range("C" & Target.Row + 1).Select
  
  End If
  
End Sub

 

[Peter_SSs]

And another thing, While I select all the data and Delete it shows error like this,

My mistake on that one. It should have been

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

A similar issue could possibly arise with @HighAndWilder's code, though less likely currently due to my last sentence below.

Checking for the enter key without actual entry would be considerably trickier. Let's see what you think of the post 5 suggestion. One thing with that would be that if you wanted to select the whole worksheet or any range that includes any cell in column F, you would not be able to.

 

[tree5ppm]

It saves the existing setting for the direction that a Enter press takes the active cell and then changes it back again when the
worksheet is deactivated. You need to deactivate and then activate the worksheet to do this.

Yeah @HighAndWilder . Great Idea, But the code you provided didn't work... The default settings is Down. It works only down with the code you provided.
Excel 2021 build 2108

Do you want to enter anything into column F?

No. I may need to enter from C,D and E.. When the pointer reach E and I press Enter it should come down to the next row

BR

 

[tree5ppm]

My mistake on that one. It should have been

This code works fine without any error while we deleting the data's.

One thing with that would be that if you wanted to select the whole worksheet or any range that includes any cell in column F, you would not be able to.

Sorry, I could not understand it clearly.

BR

 

[Peter_SSs]

Sorry, I could not understand it clearly.

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.

But the code you provided didn't work...

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

You need to deactivate and then activate the worksheet to do this.

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

 

[HighAndWilder]

Yeah @HighAndWilder . Great Idea, But the code you provided didn't work... The default settings is Down. It works only down with the code you provided.
Excel 2021 build 2108



No. I may need to enter from C,D and E.. When the pointer reach E and I press Enter it should come down to the next row

BR

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.