You'd have to capture those two keystrokes and then do a big Select
Case statement to move to the desired "next" cell depending on which
cell you're currently in.  A lot of trouble when you can just click
the cells.  Either way, I'd Google capturing keystrokes and go from
there...  I would just code this for you myself, but I just don't have
time right now... Sorry =/

Cory

On Nov 21, 10:47 am, officegirl77
<officegir...@discussions.microsoft.com> wrote:

I think this works for Tabs.  Let me know whether it does what you
want.

First, in your ThisWorkbook module, paste the following event code:

' begin workbook code

Private Sub Workbook_Open()
 Application.OnKey "{TAB}", "ProcessTab"
 Application.OnKey "{ENTER}", "ProcessTab"
 Application.OnKey "+{TAB}", "ProcessReverseTab"
End Sub

Private Sub Workbook_Activate()
 Application.OnKey "{TAB}", "ProcessTab"
 Application.OnKey "{ENTER}", "ProcessTab"
 Application.OnKey "+{TAB}", "ProcessReverseTab"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Application.OnKey "{TAB}"
 Application.OnKey "{ENTER}"
 Application.OnKey "+{TAB}"
End Sub

Private Sub Workbook_Deactivate()
 Application.OnKey "{TAB}"
 Application.OnKey "{ENTER}"
 Application.OnKey "+{TAB}"
End Sub

' end workbook code

Next, insert a standard module, and paste this code there:

' begin code

Private Const debugMode As Boolean = False
Private Const increment As Integer = 20
Private Const startCol As Integer = 2
Private Const endCol As Integer = 9
Private Const startRow As Integer = 16
Private Const endRow As Integer = 39

Public Sub ProcessTab()
 Dim tabOrder() As String
 Dim size As Long, position As Long
 Dim row As Integer, column As Integer

 If debugMode Then
   Debug.Print "Entering Tab"
 End If

 size = increment
 ReDim tabOrder(increment)

 position = LBound(tabOrder)
 tabOrder(position) = "H7"
 tabOrder(position + 1) = "H8"
 position = position + 1

 For row = startRow To endRow
   For column = startCol To endCol
     position = position + 1
     If position > size Then
       size = size + increment
       ReDim Preserve tabOrder(size)
     End If
     tabOrder(position) = Chr(Asc("A") - 1 + column) & row
   Next column
 Next row

 If debugMode Then
   ActiveSheet.Range("A1").Resize(1, UBound(tabOrder)).Value =
tabOrder
 End If

 If size > position Then
   ReDim Preserve tabOrder(position + 1)
 End If

 For position = LBound(tabOrder) To UBound(tabOrder)
   If tabOrder(position) = Replace(ActiveCell.Address, "$", "") Then
     If debugMode Then
       Debug.Print "Active cell found at position #" & position
     End If
     position = position + 1
     Exit For
   End If
 Next position

 If position >= UBound(tabOrder) Then
   If debugMode Then
     Debug.Print "Activating first cell"
   End If
   ActiveSheet.Range(tabOrder(LBound(tabOrder))).Select
 Else
   If debugMode Then
     Debug.Print "Activating position #" & position & _
                 " at address " & tabOrder(position)
   End If
   ActiveSheet.Range(tabOrder(position)).Select
 End If
End Sub

Public Sub ProcessReverseTab()
 Dim tabOrder() As String
 Dim size As Long, position As Long
 Dim row As Integer, column As Integer

 If debugMode Then
   Debug.Print "Entering Shift+Tab"
 End If

 size = increment
 ReDim tabOrder(increment)

 position = LBound(tabOrder) - 1

 For row = endRow To startRow Step -1
   For column = endCol To startCol Step -1
     position = position + 1
     If position > size Then
       size = size + increment
       ReDim Preserve tabOrder(size)
     End If
     tabOrder(position) = Chr(Asc("A") - 1 + column) & row
   Next column
 Next row

 ReDim Preserve tabOrder(position + 3)

 tabOrder(position + 1) = "H8"
 tabOrder(position + 2) = "H7"

 If debugMode Then
   ActiveSheet.Range("A1").Resize(1, _
                         UBound(tabOrder)).Value = tabOrder
 End If

 For position = LBound(tabOrder) To UBound(tabOrder)
   If tabOrder(position) = _
           Replace(ActiveCell.Address, "$", "") Then
     If debugMode Then
       Debug.Print "Active cell found at position #" & position
     End If
     position = position + 1
     Exit For
   End If
 Next position

 If position >= UBound(tabOrder) Then
   If debugMode Then
     Debug.Print "Activating last cell"
   End If
   ActiveSheet.Range(tabOrder(LBound(tabOrder))).Select
 Else
   If debugMode Then
     Debug.Print "Activating position #" & position & _
                 " at address " & tabOrder(position)
   End If
   ActiveSheet.Range(tabOrder(position)).Select
 End If
End Sub

' end of code

On Nov 21, 11:47 am, officegirl77
<officegir...@discussions.microsoft.com> wrote:

You could use sheet protection to get the behaviour you described.

Select the cells that you want the cursor to move through, then go to
format>cells>protection and uncheck the "locked" checkbox. Then go to
Tools>Protection and uncheck the "Select locked cells" checkbox (make
sure the "Select unlocked cells" and "Protect worksheet and contents
of loicked cells" boxes are checked.).

Tab will move exactly how you described, but getting enter to do it
enter requires another step assuming your cursor is set to move down
on Enter: use this line in the sheet activate event:

Application.MoveAfterReturnDirection = xlToRight
on deactivate:
Application.MoveAfterReturnDirection = xlDown

Or if you would like the cursor to always go to the right on enter, go
to Tools>Options>Edit and choose "Right" from the "Move selection
after Enter" item.

Cliff Edwards