I am making an Excel 2010 AddIn and am trying to add some code to the Application Event Application_WorkbookActivate(ByVal Wb As Workbook)
However, my Module inside the AddIn cannot have these events. I tried making a ClassModule with the events and then initializing it on the AddIn's load. I got this idea after reading this.
Here is what I have in the AddIn's Class "EventListener:"
Dim WithEvents app As Application
Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
Debug.Print "Workbook Activated:"; Wb.Name
Call MyAddInModule.RefreshVisibility
End Sub
And here is what I have in the module "MyAddInModule:"
Public Listener As EventListener
Public myRibbonUI As IRibbonUI
' Ribbon callback : runs when ribbon is loaded
Public Sub onLoadRibbon(ribbon As IRibbonUI)
' Get a reference to the ribbon
Set myRibbonUI = ribbon
Debug.Print "Ribbon Reference Set"
Set Listener = New EventListener
End Sub
Public Sub RefreshVisibility()
myRibbonUI.Invalidate
Debug.Print "Ribbon Invalidated"
End Sub
Unfortunately this did not work. It is not possible to declare a WithEvents
object in the normal coding module and the Class Object didn't fire as expected.
Is there a better way to have Application Events be coded into an AddIn?