VBA Code for TAB control - almost working

[lnagel]

I have 35 Rows of data in a Worksheet
The worksheet is protected
I have several cells that I need to land on when the user presses the tab key - these cells are formatted as unprotected
The last 2 cells in each row are formatted as unprotected - but I don't want to stop on them when using TAB Key
The last 2 cells in each row - I want to skip when using TAB and wrap the selection around to the first unprotected cell in the next row

I have the "TAB Control" code below in "Worksheet1(Code)" - This code works but only for the 1st and 2nd rows (TAB,TAB,SKIP IC, SKIP ID, Wraparound to next row)

Once I hit Row 3 - the entire sub seems to stop working and once again TAB will Stop and Select both IC & ID

Any help or insight you could provide would be appreciated



Private Sub Worksheet_Change(ByVal Target As Range)
Static sRg As Range
Dim ColumnOffset As Integer
'------------------------------------------------------------
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Union([IC:IC], [ID:ID])) Is Nothing Then
With Target
Application.EnableEvents = False
If Not sRg Is Nothing Then
If sRg.Column < .Column Then
ColumnOffset = 1
ElseIf .Column <> 1 Then
ColumnOffset = -1
End If
Else
ColumnOffset = 1
End If
.Offset(, ColumnOffset).Select
Application.EnableEvents = True
End With
End If
Set sRg = ActiveCell
End Sub

 

[Legacy 456155]

It was easier to simply draw up an example then to try to change your code. Maybe you can adapt this to fit your needs.
Example download: see file --> "VBA TAB Key Navigation.xlsm"

Option Explicit

Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal vKey As Long) As Integer '64 bit

Private Const VK_TAB As Long = &H9
Private Const firstColumn As Integer = 2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        If Not Intersect(Target, Range("IC:ID")) Is Nothing Then
            If Not GetKeyState(VK_TAB) = 0 Then Cells(Target.Row + 1, firstColumn).Select
        End If
    End If
End Sub

 

[lnagel]

Since I still can't get this to work - I have simplified the issue by creating a "test" worksheet

There is no other code in the workbook except Worksheet Tab Control Code

In my worksheet -

I have Columns 'A1:A10' , 'B1:B10' and 'C1:C10' formatted as "Unlocked" Protection
There are 10 rows in the active worksheet
Everything else in the Worksheet is formatted as "Locked" Protection
The Worksheet itself is set to "Protect Sheet"

I want the user to be able to manually select cells in all 3 columns - which works
I want the tab order to Skip Columns B & C when the user presses the tab (should wrap to column A on tab press)

I have the following code which is supposed to do that - and it does - kinda

I can get the code to skip column B on tab press with no problems
Column C however - No matter what I do - I cant get the code to skip column C and wrap to A

I keep thinking the issue is with the If Not Intersect line
If Not Intersect(ActiveCell, Range("B1:C10")) Is Nothing Then
I have also tried this line of code using 'Union' instead of 'Range' but still get the same result - Column B skips - Column C does not

Any help or insight or alternative method you have would be GREATLY appreciated
Thanks much

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static sRg As Range
    Dim ColumnOffset As Integer
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(ActiveCell, Range("B1:C10")) Is Nothing Then
    With Target
        Application.EnableEvents = False
        If Not sRg Is Nothing Then
            If sRg.Column < .Column Then
                ColumnOffset = 1
            ElseIf .Column <> 1 Then
                ColumnOffset = -1
            End If
        Else
            ColumnOffset = -1
        End If
        ActiveCell.Offset(, ColumnOffset).Select
        Application.EnableEvents = True
        End With
    End If
    Set sRg = ActiveCell
End Sub

 

[lnagel]

Dataluver,

My apologies for not marking your reply as the solution.

It appears that I disregarded your response simply because I did not understand how it worked!

After I took the time to understand the code - it works beautifully!

Additionally, I modified your code a bit to contain the sub within the first 36 rows only

If Not Intersect(Target, Range("IC:ID")) Is Nothing Then
If Not GetKeyState(VK_TAB) = 0 Then Cells(Target.Row + 1, firstColumn).Select
If Target.Row > 35 Then Cells(Target.Row - 35, firstColumn).Select
End If

Thanks so very much!

For any of you watching this thread (even casually) - you may want to grab the code for future reference. You have no idea how hard this particular sub is to find in working order and I've about crashed my brain trying to write my own.

 

[Legacy 456155]

Well I'm glad that you took a second look.

 

[lnagel]

Option Explicit Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal vKey As Long) As Integer '64 bit Private Const VK_TAB As Long = &H9 Private Const firstColumn As Integer = 2 Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge = 1 Then If Not Intersect(Target, Range("IC:ID")) Is Nothing Then If Not GetKeyState(VK_TAB) = 0 Then Cells(Target.Row + 1, firstColumn).Select End If End If End Sub

The Tab function to skip Rows IC:ID works flawlessly - However It appears that I do on occasion need to "manually" select Cells IC:ID

With the current code I can select IC (or ID) one time only - Subsequent tries after the 1st manual select automatically roll to (Target.Row + 1, firstColumn)

Hoping you can take a minute on this - Thanks

 

[Legacy 456155]

Please post your adaptation of what I gave you. I don't see anything in my example that would cause that sort of behavior.

 

[lnagel]

Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal vKey As Long) As Integer '64 bit
Private Const VK_TAB As Long = &H9
Private Const firstColumn As Integer = 1
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        If Not Intersect(Target, Range("IC:ID")) Is Nothing Then 'CHANGE THE RANGE FOR EACH BUILD
            If Not GetKeyState(VK_TAB) = 0 Then Cells(Target.Row + 1, firstColumn).Select
            'If Target.Row > 35 Then Cells(Target.Row - 35, firstColumn).Select
        End If
    End If
End Sub

The code I added is marked as comment for now

I do have Option Explicit in the worksheet

 

[lnagel]

Also - Have re-examined your posted VTAB Key Navigation xlsm and it also exhibits the same behavior

Tab thru the 1st row and then try to select manually either IC or ID column

Sorry to be a pain - I have tried a couple of things that I thought might resolve but seem to be in over my head here

 

[lnagel]

Also I am using 64 Bit Microsoft Office Professional Plus 2016 (not sure if that should matter)