Stopping (IF - Then) code from jumping to another cell after enter, tab or right arrow?

The problem I'm having is the code works great, but it won't stop jumping five rows up after hitting enter, tab, or right arrow after last entry in cell (D14) it keep jumping up to cell (E7). I need the code to stop completely after I hit enter when putting data in cell (D14) and the user can choose which method they choose to keep going across to the right of cell (D14) of their choosing. See code below, thanks!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$4" Then
 Range("E11").Select
End If
If Target.Address = "$E$11" Then
 Range("J2").Select
End If
If Target.Address = "$J$2" Then
 Range("J3").Select
End If
If Target.Address = "$J$3" Then
 Range("J4").Select
End If
If Target.Address = "$J$4" Then
 Range("J6").Select
End If
If Target.Address = "$J$6" Then
 Range("D14").Select
End If
If Not Intersect(Target, Range("D14")) Is Nothing Then
 
  Exit Sub
      
   End If
End Sub

I am unable to reproduce your issue. It sounds as if you may have some cells locked and the worksheet protect, coercing a cell change.

When I add the code to my workbook and finish at D14, the focus moves to D15.

Do you mean like this?

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D14")) Is Nothing Then
    Exit Sub
End If

If Target.Address = "$E$4" Then
 Range("E11").Select
End If
If Target.Address = "$E$11" Then
 Range("J2").Select
End If
If Target.Address = "$J$2" Then
 Range("J3").Select
End If
If Target.Address = "$J$3" Then
 Range("J4").Select
End If
If Target.Address = "$J$4" Then
 Range("J6").Select
End If
If Target.Address = "$J$6" Then
 Range("D14").Select
End If

End Sub

I suspect the Wabbit - can you post your spreadsheet that exhibits this phenomenon?

I'd just make sure if D14 was the last cell changed, that it stays there:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$E$4" Then
 Range("E11").Select
End If
If Target.Address = "$E$11" Then
 Range("J2").Select
End If
If Target.Address = "$J$2" Then
 Range("J3").Select
End If
If Target.Address = "$J$3" Then
 Range("J4").Select
End If
If Target.Address = "$J$4" Then
 Range("J6").Select
End If
If Target.Address = "$J$6" Then
 Range("D14").Select
End If
If Target.Address = "$D$14" Then
 Range("D14").Select
End If

End Sub

Another way to write the same code, showcasing the Select Case structure:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$4"
        Range("E11").Select
    Case "$E$11"
        Range("J2").Select
    Case "$J$2"
        Range("J3").Select
    Case "$J$3"
        Range("J4").Select
    Case "$J$4"
        Range("J6").Select
    Case "$J$6"
        Range("D14").Select
    Case "$D$14"
        Range("D14").Select
End Select
End Sub

Originally Posted by daffodil11

I'd just make sure if D14 was the last cell changed, that it stays there:

Another way to write the same code, showcasing the Select Case structure:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$4"
        Range("E11").Select
    Case "$E$11"
        Range("J2").Select
    Case "$J$2"
        Range("J3").Select
    Case "$J$3"
        Range("J4").Select
    Case "$J$4"
        Range("J6").Select
    Case "$J$6"
        Range("D14").Select
    Case "$D$14"
        Range("D14").Select
End Select
End Sub

I tried this code too same thing happens. Darn it, there's so many other codes/modules connected to this whole workbook it's probably effecting it somehow. Ok, so I went and deleted all the remaining code modules and left only your code above. It sill does the same thing! I'll upload the file now that I deleted all the other sheets. I also deleted all "Conditional Formatting" or "Data Validation" names as well still does it?

Hi Elmer,

I tried it on my machine and Daffodil's code works for me. Have you ever repaired your office??

I totally see it now and it's driving me bonkers.

Enter data into E4, and then cycle through. Even after my code stops the selection on D14, if you hit enter again or an arrow key it immediately select J15.

No other code or EnterKeyBehavior I see throwing this thing around. Maybe your spreadsheet is haunted?

I ran this:

Sub TestCH()
Cells(4, 5) = "GGGG"
Cells(11, 5) = "June"
Cells(2, 10) = #9/11/2001#
Cells(3, 10) = 10354
Cells(4, 10) = "Canarsie"
Cells(6, 10) = "Unreparable"
Cells(14, 4) = 15
End Sub

And, it ran fine with no jump to J15

Your Whiteness,

The issue is not in the module code, but with the sheet code.

There's a change event forcing a selection from E4 to E11 to J2 to J4 to J6 to D14. Once you enter something in D14, the selection jumps to J15 despite no code requesting it.

To see it in action, start at E4 and enter data and cycle through.

Originally Posted by daffodil11

Your Whiteness,

The issue is not in the module code, but with the sheet code.

There's a change event forcing a selection from E4 to E11 to J2 to J4 to J6 to D14. Once you enter something in D14, the selection jumps to J15 despite no code requesting it.

To see it in action, start at E4 and enter data and cycle through.

Not sure who this comment is directed to. I tried merging cells J7:K7 on your worksheet you sent me and the same thing happens. Why do you think that is? If I merge J8:K8 it works perfectly. This makes no sense!

OK - Daffodil - It happened for me finally!

Elmer - I guess repairing your Office is uncalled for since the phenomena is repeated for us

Hi Elmer,

That is addressed to me (my last name is White).

Hey guys, I copied the sheet to a new book and the same thing happened

Elmer pointed out to me earlier that if you unmerge the entire worksheet, the issue goes away. I'm still baffled as to what about the merging is causing the unknown behavior.

Somehow the J7:P7 merge is making Excel thinking it needs to resume at that column.

Very odd. If you put a Stop in at the top of the code and step through, it doesn't do it.

This seems to prevent the issue ... but not ideal:

Private Sub Worksheet_Change(ByVal Target As Range)
'Stop

If Target.Address = "$E$4" Then
 Range("E11").Select
 Exit Sub
End If
If Target.Address = "$E$11" Then
 Range("J2").Select
 Exit Sub
End If
If Target.Address = "$J$2" Then
 Range("J3").Select
 Exit Sub
End If
If Target.Address = "$J$3" Then
 Range("J4").Select
 Exit Sub
End If
If Target.Address = "$J$4" Then
 Range("J6").Select
 Exit Sub
End If
If Target.Address = "$J$6" Then
 Range("D14").Select
 Exit Sub
End If

If Not Intersect(Target, Range("D14:D32")) Is Nothing Then
    Target.Offset(1, 0).Select
End If

If Not Intersect(Target, Range("D33")) Is Nothing Then
    Range("N20").Select
End If

If Not Intersect(Target, Range("N14:N33")) Is Nothing Then
    Target.Offset(1, 0).Select
End If

End Sub

Regards, TMS

And, when I right arrow it jumps up

OK, another attempt.

Private Sub Worksheet_Change(ByVal Target As Range)
'Stop

Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim vTarray ' target array
Dim vDarray ' destination array
Dim rTarget As Range, lMatch As Long

vTarray = Array("$E$4", "$E$11", "$J$2", "$J$3", "$J$4", "$J$6")
vDarray = Array("$E$11", "$J$2", "$J$3", "$J$4", "$J$6", "$D$14")
Set rTarget = Target(1, 1)

' check if Target is empty or if the cell count > 1 (merged cells?)
If Len(rTarget.Value) = 0 Or Target.Cells.Count > 1 Then
    rTarget.Offset(1, 0).Select
    Exit Sub
End If

' determine which "cell of Interest" has been changed
On Error Resume Next
lMatch = awf.Match(rTarget.Address, vTarray, 0)
If Err.Number = 0 Then
    ' of interest
    Range(vDarray(lMatch - 1)).Select
Else
    ' not of interest (select next cell down)
    rTarget.Offset(1, 0).Select
End If

End Sub

I'm guessing, as others have, that the issue is around the fact that some cells are merged. Consequently, the Target "cell" may actually be two cells and the selection is getting confused.

It should be simpler to scale up the requirement with this approach as there are two lists of cell addresses; target cell and destination cell.

Seems to work but I'll leave it to you guys to destruction test it.

Regards, TMS

Slight improvement ...

Private Sub Worksheet_Change(ByVal Target As Range)
'Stop

Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim vTarray ' target array
Dim vDarray ' destination array
Dim rTarget As Range, lMatch As Long

' Move Down
Const clRoffset As Long = 1     ' Row Offset for Move
Const clCoffset As Long = 0     ' Column Offset for Move
' Move Right
'Const clRoffset As Long = 0     ' Row Offset for Move
'Const clCoffset As Long = 1     ' Column Offset for Move


vTarray = Array("$E$4", "$E$11", "$J$2", "$J$3", "$J$4", "$J$6", "$D$33")
vDarray = Array("$E$11", "$J$2", "$J$3", "$J$4", "$J$6", "$D$14", "$N$14")
Set rTarget = Target(1, 1)

' check if Target is empty or if the cell count > 1 (merged cells?)
If Len(rTarget.Value) = 0 Or Target.Cells.Count > 1 Then
    rTarget.Offset(clRoffset, clCoffset).Select
    Exit Sub
End If

' determine which "cell of Interest" has been changed
On Error Resume Next
lMatch = awf.Match(rTarget.Address, vTarray, 0)
If Err.Number = 0 Then
    ' of interest
    Range(vDarray(lMatch - 1)).Select
Else
    ' not of interest (select next cell down)
    rTarget.Offset(clRoffset, clCoffset).Select
End If

End Sub

Regards, TMS

Well I must say that's interesting, hitting tab, enter, or arrow all jumps below Cell D14. Seems like were getting closer anyways. Ideally would like it to just stop after D14 and any method used by the user works normally as it should. If you hit tab after putting in data in cell D14 it will continue on to the right, If you hit right arrow key it goes to the right of D14, If you happen to hit enter then it obviously goes directly under cell D14. Thanks to all tho, what a simple yet complex situation to find a good remedy!

Well I must say that's interesting, hitting tab, enter, or arrow all jumps below Cell D14.

Not in my testing. Please see the attached updated example.

If this does not fix the problem for you, I suggest you restructure the sheet and lose the merged cells between columns D and E. I suspect that is the root cause of the problem.

Regards, TMS

OK, I give in. The only way I can get any untoward (but understandable) behaviour is if I protect the sheet and prevent selection of locked cells. But, even then, it goes where I would expect it to go. Is the sheet protected? Are you using the code in a workbook other than the sample you uploaded? Is there any other code in place, especially event handlers?

When you say:

and hitting enter, right arrow or tab jumps down one cell below cell D14.

what cell is selected when you press any of those keys?


Regards, TMS