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 456129]

Put that info in your profile by selecting the applying version and type of OS. It will show up below your name in the left sidebar.

 

[Legacy 456155]

'If Target.Row > 35 Then Cells(Target.Row - 35, firstColumn).Select

Of course I don't know exactly how your workbook is set up, but the example I used doesn't exhibit that behavior. I feel like I'm missing something? I added the following to my example to return to the first cell.

            Application.EnableEvents = False
            If Not GetKeyState(VK_TAB) = 0 Then Cells(Target.Row + 1, firstColumn).Select
            If Selection.Locked Then Range("B2").Select
            Application.EnableEvents = True

So in your code, you might try replacing:

If Target.Row > 35 Then Cells(Target.Row - 35, firstColumn).Select

With

If Selection.Locked Then Selection.Offset(-36).Select

 

[Legacy 456155]

I'm not real familiar with OneDrive, but I think that I can set it to allow uploads. Maybe you can upload an example of what you are using. BRB
I'm back and all I did was manage to break the link I posted earlier!
Here's Another Link

 

[lnagel]

hmmmm - This is odd

I added your code

Application.EnableEvents = False
            If Not GetKeyState(VK_TAB) = 0 Then Cells(Target.Row + 1, firstColumn).Select
            If Selection.Locked Then Range("A2").Select
            Application.EnableEvents = True

and all of my issues have seemed to disappear - doesn't make a whole lot of sense to me but I'm gonna run with it for now

Thanks so much - again!

 

[Legacy 456155]

Hey! As long as it works! Until it doesn't.

 

[lnagel]

Would you happen to be a Vintage Baseball Collector?

My actual project is an approximate 2 year endeavor building a pretty cool Vintage Card Management piece

If interested take a look at setbilder.com

If you see something you like - let me know and I would be happy to send

 

[Legacy 456155]

Would you happen to be a Vintage Baseball Collector?

No. I'm more into getting rid of stuff. I appreciate the kind gesture, but I'm good. Have a good 1!