Navigate through cells

[Lux Aeterna]

Hi everyone,

I have an Excel sheet where all cells are locked except for a few unlocked ones. I navigate through these unlocked cells using the Enter key. I need a VBA macro that will enable me to navigate from cell F17 to B3 and from cell E26 to F21 when pressing Enter, without scrolling the view.

Thanks!

 

[dmt32]

Hi everyone,

I have an Excel sheet where all cells are locked except for a few unlocked ones. I navigate through these unlocked cells using the Enter key. I need a VBA macro that will enable me to navigate from cell F17 to B3 and from cell E26 to F21 when pressing Enter, without scrolling the view.

Thanks!

Hi
give following a try & see if will do what you want

Place ALL following code in a STANDARD module

Public Const TabSheet As String = "Sheet1"
 Sub SetOnkey(ByVal State As Integer)
'Ver 2 2014 - Dave Timms (aka DMT32) and  Jerry Sullivan MVP

    If State = xlOn Then
        With Application
            .OnKey "{TAB}", "'TabRange xlNext'"      'Tab key
            .OnKey "+{TAB}", "'TabRange xlPrevious'" 'Shift + Tab Key
            .OnKey "~", "'TabRange xlNext'"          'Enter Key
            .OnKey "{RIGHT}", "'TabRange xlNext'"    'Right Arrow Key
            .OnKey "{LEFT}", "'TabRange xlPrevious'" 'Left Arrow Key
            .OnKey "{DOWN}", "do_nothing"
            .OnKey "{UP}", "do_nothing"
        End With
    Else
'reset keys
        With Application
            .OnKey "{TAB}"
            .OnKey "~"
            .OnKey "{RIGHT}"
            .OnKey "{LEFT}"
            .OnKey "{DOWN}"
            .OnKey "{UP}"
        End With
    End If
End Sub


Sub do_nothing()
    'nothing to do
End Sub

Sub TabRange(ByVal TabDirection As XlSearchDirection)
    '  Ver 2 2014 - Dave Timms (aka DMT32) and  Jerry Sullivan MVP
    
    Dim vTabOrder   As Variant, m As Variant, i As Long
    
    vTabOrder = TabArray
    On Error GoTo exitsub
    m = Application.Match(ActiveCell.Address(0, 0), vTabOrder, False)
    'if activecell is not in Taborder array start at first cell
    If IsError(m) Then
        'goto first cell in array
        i = LBound(vTabOrder)
    Else
        'get corresponding array index
        i = m + LBound(vTabOrder) - 1
        'increment i value based on tabdirection
        i = i + IIf(TabDirection = xlPrevious, -1, xlNext)
        'ensure stay within array bounds
        If i > UBound(vTabOrder) Then i = LBound(vTabOrder)
        If i < LBound(vTabOrder) Then i = UBound(vTabOrder)
    End If
    'select cell based on array element
    Application.EnableEvents = False
    Range(vTabOrder(i)).Select
        
exitsub:
    Application.EnableEvents = True
End Sub

Function TabArray() As Variant
    'set the tab order of input cells - change ranges as required
    TabArray = Array("B3", "C3", "D3", "E3", "F3", "B4", "C4", "D4", "E4", "F4", "B5", "C5", "D5", "E5", _
                     "F5", "B6", "C6", "D6", "E6", "F6", "B7", "C7", "D7", "E7", "F7", "B8", "C8", "D8", _
                     "E8", "F8", "B9", "C9", "D9", "E9", "F9", "B10", "C10", "D10", "E10", "F10", "B11", _
                     "C11", "D11", "E11", "F11", "B12", "C12", "D12", "E12", "F12", "B13", "C13", "D13", _
                     "E13", "F13", "B14", "C14", "D14", "E14", "F14", "B15", "C15", "D15", "E15", "F15", _
                     "B16", "C16", "D16", "E16", "F16", "B17", "C17", "D17", "E17", "F17", "E21", "F21", _
                     "E22", "F22", "E23", "F23", "E24", "F24", "E25", "F25", "E26", "F26")
End Function

Change SHEET NAME shown in BOLD as required

Place following code in the Thisworkbook Code Page

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If Sh.Name = TabSheet Then SetOnkey xlOn
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    If ActiveSheet.Name = TabSheet Then SetOnkey xlOn
End Sub
 

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    SetOnkey xlOff
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Name = TabSheet Then SetOnkey xlOff
End Sub

Taken from solution first posted here: VBA Code for Tab order should allow you to navigate specified ranges using Enter, Tab, Left & Right arrow keys.

I have already populated your ranges in the TabArray function hopefully in correct order but you can update if needed.

Hope helpful

Dave