我手头有几本在VBA方面几乎相同、但数据集不同的工作簿。我有一个宏，能像浏览器那样，使用快捷键（Ctrl + Tab、Ctrl + Shift + Tab）在同一个工作簿中的工作表之间切换。

只有一个工作簿打开时，一切都很顺利。然而，如果同时打开（并关闭）不止一个工作簿，情况就会变得混乱。宏仍然按应有方式工作，但如果我之前已经打开过一个并且已经关闭它，执行时会再打开另一个工作簿。让人难以理解的一点是，脚本总是能工作，即它在正确的工作簿中切换标签页，但在切换标签之前，另一个文件就已经被打开了。比如：

打开文件A

打开文件B

关闭文件B

在文件A 中运行宏

--> 文件B 打开

--> 标签页在文件A 中切换（如应有）

如果两个文件都打开，宏按应有方式工作。问题只在另一个文件曾经打开过并已关闭时才会出现。

这是我现在的代码。以前尝试改为引用ThisWorkbook和 ActiveWorkbook时，代码确实更简单，但我得出的结论是问题似乎在实际代码之外，因为在执行任何代码之前，另一个文件就已打开。

值得一提的是，我需要能够复制工作簿模板，因此不能仅仅逐个重命名宏。

有什么可以尝试的做法吗？谢谢。

Sub NextSheet()
wb = ActiveWorkbook.Name
Workbooks(wb).Activate
If Workbooks(wb).ActiveSheet.Index = Workbooks(wb).Worksheets.Count Then
Workbooks(wb).Worksheets(1).Select

Else
Workbooks(wb).ActiveSheet.Next.Select
End If
End Sub


Sub PrevSheet()
wb = ActiveWorkbook.Name
Workbooks(wb).Activate
If Workbooks(wb).ActiveSheet.Index = 1 Then
Workbooks(wb).Worksheets(Worksheets.Count).Select

Else

Workbooks(wb).ActiveSheet.Previous.Select
End If
End Sub

解决方案

下一张或前一张工作表

• 你可以使用 Ctrl+Page Down 或 Ctrl+Page Up 分别选择下一张或上一张工作表。
• 使用这些快捷键，你不能选择 最后一张之后的第一张 或 第一张之前的最后一张，这是下面的代码所实现的。

ThisWorkbook 模块

Option Explicit

Private Sub Workbook_Activate()
    EnableNextSheet
    EnablePreviousSheet
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DisableNextSheet
    DisablePreviousSheet
End Sub

Private Sub Workbook_Deactivate()
    DisableNextSheet
    DisablePreviousSheet
End Sub

Private Sub Workbook_Open()
    EnableNextSheet
    EnablePreviousSheet
End Sub

标准模块，例如 Module1

Option Explicit
Option Private Module ' the 4 procedures won't appear in the Macro Dialog

Private Const NEXT_MACRO_NAME As String = "SelectNextSheet"
Private Const NEXT_KEYS As String = "^{TAB}"
Private Const PREVIOUS_MACRO_NAME As String = "SelectPreviousSheet"
Private Const PREVIOUS_KEYS As String = "^+{TAB}"

Sub EnableNextSheet()
    Application.OnKey NEXT_KEYS, NEXT_MACRO_NAME
End Sub

Sub DisableNextSheet()
    Application.OnKey NEXT_KEYS ' restore default behavior
End Sub

Sub EnablePreviousSheet()
    Application.OnKey PREVIOUS_KEYS, PREVIOUS_MACRO_NAME
End Sub

Sub DisablePreviousSheet()
    Application.OnKey PREVIOUS_KEYS ' restore default behavior
End Sub

Private Sub SelectNextSheet()

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim SheetsCount As Long: SheetsCount = wb.Sheets.Count
    If SheetsCount = 1 Then Exit Sub ' one sheet only

    Dim oIndex As Long: oIndex = wb.ActiveSheet.Index
    Dim nIndex As Long: nIndex = oIndex

    Dim nsh As Object ' worksheets and charts

    Do
        If nIndex = SheetsCount Then ' the last sheet
            nIndex = 1 ' the next is the first
        Else ' not the last sheet
            nIndex = nIndex + 1
        End If
        If nIndex = oIndex Then Exit Sub ' no other visible sheet
        Set nsh = wb.Sheets(nIndex)
        If nsh.Visible = xlSheetVisible Then
            wb.Activate
            nsh.Select
            Exit Sub
        'Else ' new sheet not visible
        End If
    Loop

End Sub

Private Sub SelectPreviousSheet()

    Dim wb As Workbook: Set wb = ThisWorkbook

    Dim SheetsCount As Long: SheetsCount = wb.Sheets.Count
    If SheetsCount = 1 Then Exit Sub ' one sheet only

    Dim oIndex As Long: oIndex = wb.ActiveSheet.Index
    Dim nIndex As Long: nIndex = oIndex

    Dim nsh As Object ' worksheets and charts

    Do
        If nIndex = 1 Then ' the first sheet
            nIndex = SheetsCount ' the previous is the last
        Else ' not the first sheet
            nIndex = nIndex - 1
        End If
        If nIndex = oIndex Then Exit Sub ' no other visible sheet
        Set nsh = wb.Sheets(nIndex)
        If nsh.Visible = xlSheetVisible Then
            wb.Activate
            nsh.Select
            Exit Sub
        'Else ' new sheet not visible
        End If
    Loop

End Sub