Skipping hidden cells in Excel Custom Tab Order

Question

I have a macro-enabled Workbook with a custom tab order delineated in VBA, which has been working swimmingly, but a need has arisen to conditionally hide some of the rows based on user input in a given cell -- that's coded in a separate subroutine and works perfectly. Unfortunately, some of the cells delineated in that custom tab order though, are located in the hidden rows. I need to add in a conditional check to see if the next cell in the tab array is in a hidden row, and to then skip ahead to the next unhidden cell in the array. Here are the relevant bits of code to enable the custom tabbing:

Private Sub EnableTabbing()

Dim TabOrder As Variant
Dim TabOrderReference As Variant
Dim Index As Long
Dim Cells As Range
Dim Cell As Range


On Error Resume Next
TabOrder = ActiveSheet.TabOrder
On Error GoTo 0
If IsEmpty(TabOrder) Then Exit Sub


mTabOrder = Array()
For Each TabOrderReference In TabOrder
    Set Cells = ActiveSheet.Range(TabOrderReference)
    For Each Cell In Cells.Cells
        If Cell.Address = Cell.MergeArea(1, 1).Address Then
            ReDim Preserve mTabOrder(LBound(mTabOrder) To UBound(mTabOrder) + 1)
            Set mTabOrder(UBound(mTabOrder)) = Cell
        End If
    Next Cell
Next TabOrderReference


Application.OnKey "{TAB}", "ThisWorkbook.MoveToNextTabLocation"
Application.OnKey "+{TAB}", "ThisWorkbook.MoveToPreviousTabLocation"
If Application.MoveAfterReturn Then
    Application.OnKey "~", "ThisWorkbook.MoveToNextTabLocation"
    Application.OnKey "+~", "ThisWorkbook.MoveToPreviousTabLocation"
    Application.OnKey "{ENTER}", "ThisWorkbook.MoveToNextTabLocation"
    Application.OnKey "+{ENTER}", "ThisWorkbook.MoveToPreviousTabLocation"
End If

End Sub

And then to move from one tab to the next:

Private Sub MoveToNextTabLocation()

Dim SelectNextCell As Boolean
Dim Index As Long
For Index = LBound(mTabOrder) To UBound(mTabOrder)
    If SelectNextCell Then
        mTabOrder(Index).Activate
        Exit Sub
    End If
    If mTabOrder(Index).Address = ActiveCell.Address Then SelectNextCell = True
Next Index
mTabOrder(LBound(mTabOrder)).Activate

End Sub

To my thinking, this should simply require an If statement to add that check, presumably into the MoveToNextTabLocation sub -- Something along the lines of:

If ActiveCell.EntireRow.Hidden = True Then SelectNextCell = True

Though I know that's not really looking ahead to the next cell in sequence, and I can't get the syntax right for where it would need to be placed in the code. What makes the most sense to skip any cells in a hidden row?

Skipping hidden cells in Excel Custom Tab Order

0 Answers