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.
CodePudding user response:
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