[VBA] How to Jump to a Specific Cell by Pressing “F9” Within a Specific Range

Background

A colleague asked me for a specific feature: “When the cursor is between cells F9 and F16, I want the cursor to jump to J7 when I press a specific key.”

I implemented this using VBA. In this example, I used the F9 key as the trigger.

The Code

Here is the VBA code. You need to paste the code into two different places: the Sheet Module and a Standard Module.

Sheet Module (e.g., Sheet1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' Check if the active cell is within the range F9:F16
    If Not Intersect(Target, Me.Range("F9:F16")) Is Nothing Then
        ' If inside the range, assign the F9 key to run the "MoveToJ7" macro
        Application.OnKey "{F9}", "MoveToJ7" 
    Else
        ' If outside the range, restore the normal function of the F9 key
        Application.OnKey "{F9}" 
    End If
End Sub

Private Sub Worksheet_Deactivate()
    ' Restore the normal function of the F9 key when leaving this worksheet
    Application.OnKey "{F9}"
End Sub

Standard Module (e.g., Module1)

Sub MoveToJ7()
    ' Move the cursor to cell J7
    ' Change "Sheet1" to your actual sheet name if necessary
    Sheets("Sheet1").Range("J7").Select 
End Sub

Code Explanation

1. Worksheet_SelectionChange Event

• Target: Represents the currently selected cell(s).
• Intersect(Target, Me.Range("F9:F16")): This checks if the selected cell overlaps with the range F9:F16.
• Application.OnKey "{F9}", "MoveToJ7": If the cursor is inside the target range, this command changes the behavior of the F9 key to execute the MoveToJ7 procedure instead of calculating formulas.
• Else ... Application.OnKey "{F9}": If the cursor moves out of the range, this command resets the F9 key to its default behavior.

2. Worksheet_Deactivate Event

• It is important to reset the key binding when you switch to a different sheet. This event ensures that F9 works normally on other sheets.

3. MoveToJ7 Subroutine

• This simple macro selects cell J7. You can customize the destination cell or the sheet name as needed.

Summary

In this example, I assigned the action to the F9 key, but you can change "{F9}" to any other key you prefer. This technique is useful for creating custom navigation shortcuts within specific forms or tables.