Search code examples
excelvbacell

How to Jump to a desired cell in excel when enter is pressed?

I have a situation, where I'll start from cell E5 -> H5 -> E7 -> H7 -> E9 -> H9 and then back to E5

What is the VBA code to do this?

[Cells entry flow

Solution

  • @cybernetic.nomad comment seems like a better route to take IMO although you will have to re-order the entries to tab in the correct order.

    Here is a VBA solution regardless that will always snap to the next cell in your flow photo when one of your flow cells is changed

    Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub

Select Case Target.Address(False, False)
    Case "E5"
        Range("H5").Select
    Case "H5"
        Range("H7").Select
    Case "H7"
        Range("H9").Select
    Case "H9"
        Range("E9").Select
    Case "E9"
        Range("E7").Select
    Case "E7"
        Range("E5").Select
End Select

End Sub

    I always had a hard time figuring out when .Select was necessary after my first thorough shaming for it's use when I first started learning VBA. This would be the 2nd valid reason I've came across :0