Trap workbooks events from Excel add-in-CodePudding

I try to create an add-in, mostly as a POC, but it does not work. What am I missing ?
I just want the add-in to debug.print the name of the workbook when it is activated.
The class self instantiating, thanks to the PredeclaredId attribute set to True (the part before Option Explicit only works if you IMPORT the class, it cannot be typed in the VBE).
Here is the class module:

VERSION 1.0 CLASS
BEGIN
  MultiUse = 0
END
Attribute VB_Name = "clsWb"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Option Explicit

Private WithEvents myApp As Application
Attribute myApp.VB_VarHelpID = -1

Private Sub Class_Initialize()
    Set myApp = Application
End Sub

Private Sub myApp_WorkbookActivate(ByVal Wb As Workbook)
    Debug.Print Now, Wb.Name
End Sub

CodePudding user response：

No other class is needed to accomplish what you want. Please, try proceeding in the next way:

Copy the next code in add-in ThisWorkbook code module:

Public WithEvents myApp As Application

Sub ActivateMyAppH()
    Set myApp = Application
End Sub

Sub InAactivateMyAppH()
    Set myApp = Nothing
End Sub

Private Sub myApp_WorkbookActivate(ByVal Wb As Workbook)
    Debug.Print Now, Wb.Name
End Sub

(Firstly, manually) run ActivateMyAppH and play with activating documents.

After seeing it working, place a call in the add-in Workbook_Open event:

Private Sub Workbook_Open()
   ActivateMyAppH
   'whatever necessary, if the event is already used for something else
End Sub

To be on the safe side, take care to inactivate the event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   InAactivateMyAppH
End Sub