Tab - Back Tab Question

[Jenny B.]

Hello,

I’m 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

 

[Jim Cone]

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,

I’m 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

 

[Jenny B.]

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.