excelvbaexcel-addins

Events on XLAM Add-in not connected to Workbook

I made this test project, this is my standard module level code

 ' Declare a module-level variable to store the Application object
 Public App As Application

' Procedure to initialize the App variable:
Sub StartAppEvents()
    Set App = Application
End Sub

and this code on ThisWorkBook

Private Sub Workbook_Open()
    StartAppEvents
End Sub

Private Sub App_WorkbookSheetChange(ByVal Wb As Workbook, ByVal Sh As Object, ByVal Target As Range)
   MsgBox "App_WorksheetChange"
End Sub

XLAM

Than I open new empty book and add this XLAM Add-in to new workbook

Add addins

Nothing happens, Events in XLAM Add-in not connected to new Workbook.
Why? What I doing wrong?

UPD: If we adding WithEvents to App declaration - we need to move code to Class1 (Excel don't allow to use Withevents in standard modules). In this case we will receive another error:

Withevents

What we need more? I see only two opportunity - add Public keyword to StartAppEvents declaration and change Instancing parameters from standard value (Private) to PublicNotCreatable. Both changing do not fix this error.

modified code

UPD2. Unfortunately idea from Tim Williams with New Events object don't working:

Reconnect Events

Solution

  • Public App As Application

    should be

    Public WithEvents App As Application

    and it can't be in a regular code module: it needs to be in an object module such as a Class, Worksheet or maybe ThisWorkbook.

    See for example: https://learn.microsoft.com/en-us/office/vba/outlook/concepts/electronic-business-cards/using-events-with-automation#:~:text=You%20must%20use%20the%20WithEvents%20keyword%20to%20specify%20that%20the%20object%20variable%20will%20be%20used%20to%20respond%20to%20events%20triggered%20by%20the%20object.

    For example this pops up a msgbox whenever I edit a sheet

    ThisWorkbook:

    Option Explicit

Private AppEvt As AppEvents

Private Sub Workbook_Open()
    Set AppEvt = New AppEvents
    Set AppEvt.App = Application
End Sub

    Class module (insert a new class module and change its name to AppEvents):

    Option Explicit

Public WithEvents App As Excel.Application

'there's no "workbooksheetchange" event....
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "App_WorksheetChange"
End Sub

    To make sure you're using a valid Application event, first select App from the left-hand drop-down at the top of the class module, then select an available event from the right-hand drop-down.