Public Const TabSheet As String = "YourSheetName"
Sub SetOnkey(ByVal state As Integer)
' Ver 2 2014
' Authors Dave Timms (aka DMT32) and Jerry Sullivan MVP
If state = xlOn Then
With Application
.OnKey "{TAB}", "'TabOrder xlNext'" 'Tab key
.OnKey "+{TAB}", "'TabOrder xlPrevious'" 'Shift + Tab Key
.OnKey "~", "'TabOrder xlNext'" 'Enter Key
.OnKey "{RIGHT}", "'TabOrder xlNext'" 'Right Arrow Key
.OnKey "{LEFT}", "'TabOrder 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 TabOrder(ByVal Direction As XlSearchDirection)
' Ver 2 2014
' Authors Dave Timms (aka DMT32) and Jerry Sullivan MVP
Dim m As Variant, i As Long
On Error Resume Next
m = Application.Match(ActiveCell.Address(0, 0), TabOrderArray, False)
On Error GoTo exitsub
'if activecell is not in Taborder array start at first cell
If IsError(m) Then
'goto first cell in array
i = LBound(TabOrderArray)
Else
'get corresponding array index
i = m + LBound(TabOrderArray) - 1
'increment i value based on tab direction
i = i + IIf(Direction = xlPrevious, -1, xlNext)
'ensure stay within array bounds
If i > UBound(TabOrderArray) Then i = LBound(TabOrderArray)
If i < LBound(TabOrderArray) Then i = UBound(TabOrderArray)
End If
'select cell based on array element
Application.EnableEvents = False
Range(TabOrderArray(i)).Select
exitsub:
Application.EnableEvents = True
End Sub
Function TabOrderArray() As Variant
TabOrderArray = Array("B7", "Z7", "B8", "Z8", "B9", "Z9", "C7", "AA7")
End Function