如何在Excel加载项中实现自动跟踪并返回上一活动工作表的宏

Absolutely, you can build this functionality purely as an Excel Add-in—no need to add any code to the ThisWorkbook module of individual non-macro-enabled workbooks! Let's fix your existing code and set up proper automatic tracking that works across any open workbook.

Step 1: Create a Class Module for Application-Level Events #

First, add a class module to your add-in project (name it AppEvents—you can rename it in the Properties window):

Option Explicit
Private WithEvents xlApp As Excel.Application
Private mPrevSheet As Worksheet
Private mPrevWB As Workbook

Private Sub Class_Initialize()
    Set xlApp = Excel.Application
    ' Start tracking with the currently active sheet when the add-in loads
    If Not xlApp.ActiveSheet Is Nothing Then
        Set mPrevWB = xlApp.ActiveWorkbook
        Set mPrevSheet = xlApp.ActiveSheet
    End If
End Sub

Private Sub xlApp_SheetActivate(ByVal Sh As Object)
    ' Update our "previous" sheet only if we're switching to a new sheet/workbook
    If Not Sh Is mPrevSheet Or Not Sh.Parent Is mPrevWB Then
        Set mPrevWB = Sh.Parent
        Set mPrevSheet = Sh
    End If
End Sub

' Expose our tracked values to the standard module
Public Property Get PreviousSheet() As Worksheet
    Set PreviousSheet = mPrevSheet
End Property

Public Property Get PreviousWorkbook() As Workbook
    Set PreviousWorkbook = mPrevWB
End Property

Step 2: Set Up the Standard Module #

Add a standard module (e.g., Module1) to handle initialization and the navigation macro:

Option Explicit
Private appEvents As AppEvents

Sub Auto_Open()
    ' Initialize the event handler when the add-in loads
    Set appEvents = New AppEvents
End Sub

Sub GoToPreviousSheet()
    On Error Resume Next
    ' Check if we have a valid tracked sheet/workbook
    If Not appEvents.PreviousWorkbook Is Nothing And Not appEvents.PreviousSheet Is Nothing Then
        ' Activate the previous workbook first, then the sheet
        appEvents.PreviousWorkbook.Activate
        appEvents.PreviousSheet.Activate
    Else
        MsgBox "You haven't switched sheets since launching Excel with this add-in!"
    End If
    On Error GoTo 0
End Sub

How It Works #

• Application-Level Tracking: The WithEvents xlApp object lets us capture SheetActivate events across every open workbook, including non-macro-enabled ones. No need for per-workbook code.
• Reliable Storage: We store Workbook and Worksheet objects instead of just sheet names, which avoids conflicts with duplicate sheet names and handles closed workbooks gracefully.
• Automatic Initialization: The Auto_Open sub runs when the add-in loads, starting tracking immediately.

Setup Instructions #

1. Save your project as an Excel Add-in (.xlam file) from the VBA editor (File > Export File...).
2. Enable the add-in in Excel: Go to File > Options > Add-ins > Manage: Excel Add-ins > Go... > Browse to select your .xlam file.
3. You can now run the GoToPreviousSheet macro from any workbook, and it will jump to the last activated sheet—even in non-macro-enabled files.

内容的提问来源于stack exchange，提问作者arpkit