If ActiveSheet to loop through and select sheets

user_19635453 0
Viewed 37

I have a workbook that simulates an in-house program. There are two macros; one goes "forward" and one goes "backwards" to create a loop moving users to each visible sheet. For example,

Sheet 1->Sheet 10->Sheet 11->Sheet 1.

There are hidden sheets used as templates.

Sub Forward()
idx = ActiveSheet.Index
If Sheets(idx + 1).Visible = False Then
Sheets("Name Sequence").Select
Else
ActiveSheet.Next.Activate
End If
End Sub

Sub Backward()
If ActiveSheet Is Sheets(1) Then
ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count).Activate
Else
ActiveSheet.Previous.Activate
End If
End Sub

The intent is to have the macros cycle through and select visible sheets, moving the user to a different sheet each time they click the forward or backwards buttons.

vba excel
1 Answers

Select Next or Previous Visible Sheet

Sub Forward()

    Dim ash As Object: Set ash = ActiveSheet
    
    If ash Is Nothing Then
        MsgBox "There is no active sheet.", vbExclamation
        Exit Sub
    End If
    
    Dim aIndex As Long: aIndex = ash.Index
    
    Dim wb As Workbook: Set wb = ash.Parent
    Dim shCount As Long: shCount = wb.Sheets.Count
    
    Dim nsh As Object
    Dim n As Long
    
    If aIndex < shCount Then
        For n = aIndex + 1 To shCount
            Set nsh = wb.Sheets(n)
            If nsh.Visible = xlSheetVisible Then nsh.Select: Exit Sub
        Next n
    End If
    
    For n = 1 To aIndex - 1
        Set nsh = wb.Sheets(n)
        If nsh.Visible = xlSheetVisible Then nsh.Select: Exit Sub
    Next n

    MsgBox "This is the only visible sheet.", vbExclamation
    
End Sub

    
Sub Backward()

    Dim ash As Object: Set ash = ActiveSheet
    
    If ash Is Nothing Then
        MsgBox "There is no active sheet.", vbExclamation
        Exit Sub
    End If
    
    Dim aIndex As Long: aIndex = ash.Index
    
    Dim wb As Workbook: Set wb = ash.Parent
    Dim shCount As Long: shCount = wb.Sheets.Count
    
    Dim psh As Object
    Dim p As Long
    
    If aIndex > 1 Then
        For p = aIndex - 1 To 1 Step -1
            Set psh = wb.Sheets(p)
            If psh.Visible = xlSheetVisible Then psh.Select: Exit Sub
        Next p
    End If
    
    For p = shCount To aIndex + 1 Step -1
        Set psh = wb.Sheets(p)
        If psh.Visible = xlSheetVisible Then psh.Select: Exit Sub
    Next p
    
    MsgBox "This is the only visible sheet.", vbExclamation
    
End Sub
user_98140690
Related