Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 94
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,290
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 94
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get back to a question I've posted? Jim Excel Discussion (Misc queries) 2 June 6th 07 04:24 PM
copy back macro/roll back moh Excel Worksheet Functions 4 March 5th 07 03:19 PM
Macro Question - Excel to Access and back again Mike The Newb Excel Discussion (Misc queries) 1 October 19th 06 02:35 PM
I hid row 1 and can't get it back? Nyczoo Excel Discussion (Misc queries) 4 January 24th 06 08:22 PM
Jon - come back Wazooli Charts and Charting in Excel 0 March 24th 05 05:49 PM


All times are GMT +1. The time now is 01:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"