How do the cursor move automatically to a specific cell when the user enters a value and the process, Also this process should repeat in each each row

user_18642010 0
Viewed 283

Foe example, When User enters a value in A6 and press enter, cursor should move to G6. When the user enter the value in G6 the cursor should move to A7. user enter value again, cursor moves to G7. this should repeat A8 to G8, A9 to G9.

I have created a small VBA code with help of my friend! Any modification to this code will be much helpful!

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$5" Then Range("G5").Select
End Sub
excel-2010 excel-formula vba excel
3 Answers

You can change the code to :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Target.Offset(0, 6).Select
    ElseIf Target.Column = 7 Then
        Target.Offset(1, -6).Select
    End If
End Sub

However, If the user don't modify anything in a cell and just pressed Tab, the cursor will still jump to the next cell because the Change-Event is not triggered.

Alternative (without code): Format the columns A and G: Remove the flag "Locked" from the Protection Tab of the Format Dialog. All other cells should be locked. Not protect your worksheet (Review->Protect sheet).

user_75997980

Alternating Cursor Down

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const rgAddress As String = "A:A,G:G"
    Dim srg As Range: Set srg = Range(rgAddress)
    
    Dim irg As Range: Set irg = Intersect(srg, Target)
    If irg Is Nothing Then Exit Sub
    
    Dim arg As Range: Set arg = irg.Areas(irg.Areas.Count)
    Dim iCell As Range: Set iCell = arg.Cells(arg.Rows.Count, arg.Columns.Count)
    
    Dim Col1 As Long: Col1 = srg.Areas(1).Column
    Dim Col2 As Long: Col2 = srg.Areas(2).Column
    
    If iCell.Column = Col1 Then
        iCell.Offset(, Col2 - Col1).Select
    Else
        If iCell.Row = Rows.Count Then
            Cells(1, Col1).Select
        Else
            Cells(iCell.Row + 1, Col1).Select
        End If
    End If
    
End Sub
user_98140690

I would define the columns to check as const - in case there is a change to the sheet.

Then write a function that implements your logic:

  • if change in first column jump to second column in same row
  • if change in second column jump one row down back to column 1

I prefer to write seperate functions/subs to be called from within the sheet-events.

If they have good names it's clear what you want to achieve without reading the code itself.

Or maybe you can re-use them from different sheets if you put them in a general module.

Option Explicit

Private Const col1ToCheck As String = 1    'column A
Private Const col2ToCheck As String = 7    'column G


Private Sub Worksheet_Change(ByVal Target As Range)

Dim cSource As Range: Set cSource = Target.Cells(1, 1)

If cSource.Column = col1ToCheck Or cSource.Column = col2ToCheck Then
    nextCellSpecial(cSource).Select
End If

End Sub

'this function implements your "jump"-logic
Private Function nextCellSpecial(cSource As Range) As Range

Dim diffCol1ToCol2 As Long
diffCol1ToCol2 = col2ToCheck - col1ToCheck

If cSource.Column = col1ToCheck Then
    'nextCell =  offset to column 2
    Set nextCellSpecial= cSource.Offset(, diffCol1ToCol2)
    
ElseIf cSource.Column = col2ToCheck Then
    'nextCell = colName1 next row
    Set nextCellSpecial= cSource.Offset(1, -diffCol1ToCol2)
Else  'in case this function is re-used for other purposes
    Set nextCellSpecial= cSource
End If
    
End Function
user_165784240
Related