Not Selecting unlocked cells in Protected Sheet with Tab Key but working with Shift+Tab

[Vinay Sirumilani]

Hi Champs,

i am trying to make a userform, for which user could able to navigate only on unlocked cells with TAB key or SHIFT+TAB key.
Unfortunately navigation with Tab in not changing more than 2 rows but yes SHIFT+TAB is able to navigate.
in attached sample file, all shaded cells are unlocked cells and sheets is protected without password
TAB key is able to navigate between blue shaded but SHIFT_TAB key is able to navigate between all shaded cells(pink and blue both)
what could be the reason, why TAB key is not navigating on all unlocked cells.

Your support will be so helpful to me

 

[p45cal]

I can't help; this seems to have been a problem with merged cells for a while (see https://www.excelbanter.com/948137-post3.html ).
I suggest not using merged cells but using bigger single cells but realise that will mess with the table on the right and is difficult to arrange nicely.
Ideally, instead of using a 'form' made up of sheet cells, you could use a proper userform, which needs macros but that's getting into a new realm. See the attached for an example of this; the form pops up when you select the Form sheet and the tab key does what it ought to.

 

[Artik]

Draft version

In sheet Forms module:

Option Explicit

Dim LastSelCell
Dim vSequence


Private Sub Worksheet_Activate()
    If IsEmpty(LastSelCell) Then
        LastSelCell = ActiveCell.MergeArea.Address(0, 0)
    End If

    If IsEmpty(vSequence) Then
        vSequence = Split("H2,H6,L6,P6,H10,N10,H15,M15,H18,N18,H21,L21,H2", ",")
    End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim ToNext      As Variant
    Dim i           As Long

    'Debug.Print GetKeyState(VK_TAB) & "; " & GetKeyState(VK_SHIFT)
    If IsEmpty(vSequence) Then
        Call Worksheet_Activate
    End If

    If LastSelCell <> Target.Address(0, 0) Then
        If GetKeyState(VK_TAB) < 0 Or GetKeyState(VK_RETURN) < 0 Then
            If GetKeyState(VK_SHIFT) < 0 Then
                ToNext = False
            Else
                ToNext = True
            End If
        End If

        
        If IsEmpty(ToNext) Then
            LastSelCell = Target.Address(0, 0)
        ElseIf ToNext = True Then
            For i = 0 To UBound(vSequence)
                If Not Intersect(Me.Range(vSequence(i)).MergeArea, Me.Range(LastSelCell)) Is Nothing Then
                    Application.EnableEvents = False
                    Me.Range(vSequence(i + 1)).Select
                    LastSelCell = Me.Range(vSequence(i + 1)).MergeArea.Address(0, 0)
                    Application.EnableEvents = True
                    Exit For
                End If
            Next i
        ElseIf ToNext = False Then
            For i = UBound(vSequence) To 0 Step -1
                If Not Intersect(Me.Range(vSequence(i)).MergeArea, Me.Range(LastSelCell)) Is Nothing Then
                    Application.EnableEvents = False
                    Me.Range(vSequence(i - 1)).Select
                    LastSelCell = Me.Range(vSequence(i - 1)).MergeArea.Address(0, 0)
                    Application.EnableEvents = True
                    Exit For
                End If
            Next i
        Else
            Stop
        End If

        'Debug.Print LastSelCell & "; >" & ToNext & "<"
    End If
End Sub

And in standard module:

Option Explicit

#If Win64 Then
    Public Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
        Public Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If

        Public Const VK_SHIFT As Long = &H10    '= 16
        Public Const VK_TAB As Long = &H9    '= 9
        Public Const VK_RETURN As Long = &HD    '= 13

Artik