VBA Application Events in Excel AddIn

1k views Asked by At

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?

0

There are 0 answers