将导航按钮添加到Excel工作表

Last July, I posted sample Excel VBA code to navigate to the next or previous worksheet. If you're on the second sheet, you can click the Next button to go to the third sheet. Or, click the Back button to go to the first sheet.

去年7月，我发布了示例Excel VBA代码以导航到下一个或上一个工作表。 如果您在第二张纸上，则可以单击“下一步”按钮转到第三张纸。 或者，单击“后退”按钮转到第一页。

In the comments for that blog post, Ron de Bruin suggested modifying the two navigation macros, so they test if the target sheet is hidden, before selecting it.

在针对该博客文章的评论中，Ron de Bruin建议修改两个导航宏，以便在选择目标工作表之前测试它们是否被隐藏。

Finally, only 14 months later, the revised code is ready. Hey, quality work takes time! 😉

最终，仅在14个月后，修订的代码就准备就绪。 嘿，优质的工作需要时间！ 😉

Excel工作表导航代码 (Excel Worksheet Navigation Code)

Here's the Excel VBA code for the two macros -- GoSheetBack and GoSheetNext. If the next sheet is hidden, the code keeps going until it finds the next visible sheet.

这是两个宏的Excel VBA代码-GoSheetBack和GoSheetNext。 如果隐藏了下一张纸，则代码将继续进行直到找到下一张可见纸。

If it reaches the end of the sheet tabs in either direction, it jumps to the other end, and continues from there.

如果它沿任一方向到达工作表标签的末端，则跳到另一端，然后从该处继续。

'==========================
Sub GoSheetNext()
Dim wb As Workbook
Dim lSheets As Long
Dim lSheet As Long
Dim lMove As Long
Dim lNext As Long
Set wb = ActiveWorkbook
lSheets = wb.Sheets.Count
lSheet = ActiveSheet.Index
lMove = 1
With wb
  For lMove = 1 To lSheets - 1
    lNext = lSheet + lMove
    If lNext > lSheets Then
      lMove = 0
      lNext = 1
      lSheet = 1
    End If
    If .Sheets(lNext).Visible = True Then
      .Sheets(lNext).Select
      Exit For
    End If
  Next lMove
End With
End Sub
'==========================
Sub GoSheetBack()
Dim wb As Workbook
Dim lSheets As Long
Dim lSheet As Long
Dim lMove As Long
Dim lNext As Long
Set wb = ActiveWorkbook
lSheets = wb.Sheets.Count
lSheet = ActiveSheet.Index
lMove = 1
With wb
  For lMove = 1 To lSheets - 1
    lNext = lSheet - lMove
    If lNext < 1 Then
      lMove = 0
      lNext = lSheets
      lSheet = lSheets
    End If
    If .Sheets(lNext).Visible = True Then
      .Sheets(lNext).Select
      Exit For
    End If
  Next lMove
End With
End Sub
'==========================

下载样本文件 (Download the Sample File)

To see the detailed instructions, and to download the sample Navigation code workbook, please go to the Excel VBA Worksheet Macro Buttons page on the Contextures website.

若要查看详细说明，并下载示例导航代码工作簿，请转到Contextures网站上的Excel VBA工作表宏按钮页面。

观看Excel视频 (Watch the Excel Video)

To see the steps for creating the navigation macro buttons, you can watch this Excel video.

要查看创建导航宏按钮的步骤，可以观看此Excel视频。

演示地址

翻译自: https://contexturesblog.com/archives/2010/09/17/add-navigation-buttons-to-excel-worksheet/