Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab - Back Tab Question
Hello,
Im using the below Macro to restrict fields of entry to the Tab only fields noted in the application. Is there a way to modify this slightly so back tabbing can also be an option? I want to keep it basically the same as far as restricting the fields; however, I would also like to offer the user a back tab option in the event they pass up their specific field. Without that option, they are forced to tab through the entire sheet and that ends up being a pain in the backside. Thank you for your thoughts and appreciate any advice - Jenny B. Dim aTabOrd As Variant Dim iTab As Long Dim nTab As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) If IsEmpty(aTabOrd) Then aTabOrd = Array("d3", "e4", "f4", "l4", "f5", "i5", "p5", "e6", "h6", "l6", "p6", _ "e7", "j7", "q7", "f8", "j8", "f9", "l9", "g12", "h12", "k12", "g14", "h14", _ "k14", "n14", "g16", "h16", "k16", "o16", "g18", "h18", "k18", "o18", "g19", "h19", "k19", "o19", _ "g20", "h20", "g22", "h22", "k22", "g24", "h24", "k24", "n24", "g26", "n26", "g28", "h28", _ "g32", "h32", "f34") nTab = UBound(aTabOrd) + 1 iTab = 0 Else iTab = (iTab + 1) Mod nTab End If Application.EnableEvents = False Range(aTabOrd(iTab)).Select Application.EnableEvents = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab - Back Tab Question
This seems to work (shift + tab to back up)...
'-- Private Declare Function GetKeyState Lib "user32.dll" (ByVal nVirtKey As Long) As Integer Dim aTabOrd As Variant Dim iTab As Long Dim nTab As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo WrongDirection Dim blnDown As Boolean blnDown = GetKeyState(16) < 0 If IsEmpty(aTabOrd) Then aTabOrd = Array("d3", "e4", "f4", "l4", "f5", "i5", "p5", _ "e6", "h6", "l6", "p6", "e7", "j7", "q7", "f8", "j8", _ "f9", "l9", "g12", "h12", "k12", "g14", "h14", "k14", _ "n14", "g16", "h16", "k16", "o16", "g18", "h18", "k18", _ "o18", "g19", "h19", "k19", "o19", "g20", "h20", "g22", _ "h22", "k22", "g24", "h24", "k24", "n24", "g26", "n26", _ "g28", "h28", "g32", "h32", "f34") nTab = UBound(aTabOrd) + 1 iTab = 0 Else iTab = (iTab + 1 + (2 * (CLng(blnDown)))) Mod nTab If iTab < 0 Then iTab = nTab - 1 End If Application.EnableEvents = False Range(aTabOrd(iTab)).Select WrongDirection: Application.EnableEvents = True End Sub '-- Also, have you considered protecting the sheet but leaving the array cells unlocked? '-- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jenny B." wrote in message Hello, Im using the below Macro to restrict fields of entry to the Tab only fields noted in the application. Is there a way to modify this slightly so back tabbing can also be an option? I want to keep it basically the same as far as restricting the fields; however, I would also like to offer the user a back tab option in the event they pass up their specific field. Without that option, they are forced to tab through the entire sheet and that ends up being a pain in the backside. Thank you for your thoughts and appreciate any advice - Jenny B. Dim aTabOrd As Variant Dim iTab As Long Dim nTab As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) If IsEmpty(aTabOrd) Then aTabOrd = Array("d3", "e4", "f4", "l4", "f5", "i5", "p5", "e6", "h6", "l6", "p6", _ "e7", "j7", "q7", "f8", "j8", "f9", "l9", "g12", "h12", "k12", "g14", "h14", _ "k14", "n14", "g16", "h16", "k16", "o16", "g18", "h18", "k18", "o18", "g19", "h19", "k19", "o19", _ "g20", "h20", "g22", "h22", "k22", "g24", "h24", "k24", "n24", "g26", "n26", "g28", "h28", _ "g32", "h32", "f34") nTab = UBound(aTabOrd) + 1 iTab = 0 Else iTab = (iTab + 1) Mod nTab End If Application.EnableEvents = False Range(aTabOrd(iTab)).Select Application.EnableEvents = True End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Tab - Back Tab Question
What a terrific idea!
I just protected the other cells and that works much better than the restricted Tab Macro. Thank you so much and I really appreciate the great advice Jenny B. "Jim Cone" wrote: This seems to work (shift + tab to back up)... '-- Private Declare Function GetKeyState Lib "user32.dll" (ByVal nVirtKey As Long) As Integer Dim aTabOrd As Variant Dim iTab As Long Dim nTab As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo WrongDirection Dim blnDown As Boolean blnDown = GetKeyState(16) < 0 If IsEmpty(aTabOrd) Then aTabOrd = Array("d3", "e4", "f4", "l4", "f5", "i5", "p5", _ "e6", "h6", "l6", "p6", "e7", "j7", "q7", "f8", "j8", _ "f9", "l9", "g12", "h12", "k12", "g14", "h14", "k14", _ "n14", "g16", "h16", "k16", "o16", "g18", "h18", "k18", _ "o18", "g19", "h19", "k19", "o19", "g20", "h20", "g22", _ "h22", "k22", "g24", "h24", "k24", "n24", "g26", "n26", _ "g28", "h28", "g32", "h32", "f34") nTab = UBound(aTabOrd) + 1 iTab = 0 Else iTab = (iTab + 1 + (2 * (CLng(blnDown)))) Mod nTab If iTab < 0 Then iTab = nTab - 1 End If Application.EnableEvents = False Range(aTabOrd(iTab)).Select WrongDirection: Application.EnableEvents = True End Sub '-- Also, have you considered protecting the sheet but leaving the array cells unlocked? '-- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Jenny B." wrote in message Hello, Im using the below Macro to restrict fields of entry to the Tab only fields noted in the application. Is there a way to modify this slightly so back tabbing can also be an option? I want to keep it basically the same as far as restricting the fields; however, I would also like to offer the user a back tab option in the event they pass up their specific field. Without that option, they are forced to tab through the entire sheet and that ends up being a pain in the backside. Thank you for your thoughts and appreciate any advice - Jenny B. Dim aTabOrd As Variant Dim iTab As Long Dim nTab As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) If IsEmpty(aTabOrd) Then aTabOrd = Array("d3", "e4", "f4", "l4", "f5", "i5", "p5", "e6", "h6", "l6", "p6", _ "e7", "j7", "q7", "f8", "j8", "f9", "l9", "g12", "h12", "k12", "g14", "h14", _ "k14", "n14", "g16", "h16", "k16", "o16", "g18", "h18", "k18", "o18", "g19", "h19", "k19", "o19", _ "g20", "h20", "g22", "h22", "k22", "g24", "h24", "k24", "n24", "g26", "n26", "g28", "h28", _ "g32", "h32", "f34") nTab = UBound(aTabOrd) + 1 iTab = 0 Else iTab = (iTab + 1) Mod nTab End If Application.EnableEvents = False Range(aTabOrd(iTab)).Select Application.EnableEvents = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get back to a question I've posted? | Excel Discussion (Misc queries) | |||
copy back macro/roll back | Excel Worksheet Functions | |||
Macro Question - Excel to Access and back again | Excel Discussion (Misc queries) | |||
I hid row 1 and can't get it back? | Excel Discussion (Misc queries) | |||
Jon - come back | Charts and Charting in Excel |