Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can someone help me with code for tab/enter stops?
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 the ' 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))).Sele ct 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))).Sele ct 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 wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can someone make code for tab/enter stops for me please? | Excel Discussion (Misc queries) | |||
VB Code stops working | Excel Programming | |||
Code stops when high lighting (Chips code) | Excel Programming | |||
Code Stops Prematurely | Excel Programming | |||
(trying again...) code stops executing | Excel Programming |