can someone help me with code for tab/enter stops?

[Guest]

I've created an Invoice in Excel and I'd like my tab or enter stops to be as
follows:
Start on H7
then go to H8
then B16, C16, D16, E16, F16, G16, H16, I16
then B17, C17, D17, E17, F17, G17, H17, I17
repeating this until last row is
B39, C39, D39, E39, F39, G39, H39, I39


Thank you very much in advance, this will help so much!

 

[crferguson]

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

 

[ilia]

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

 

[ward376]

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