vba - How can an Excel Add-In respond to events in any worksheet?

Question

Our workbooks are server-generated SpreadsheetML, which cannot include any VBA code. Creating native Excel files or Excel 2007 XML files is also not an option, unfortunately.

So, I have an Excel Add-In (VBA, not XLL) that each of our users installs to add some extra UDFs, etc. that our workbooks need.

This works great, but now I need to have a macro that needs to execute every time the user changes the text in any cell, regardless what workbook they are actively using at the time.

So far, I've created a new Class module (SheetChangeHandler) in my Add-In, with the following code:

Option Explicit
Private WithEvents App As Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub App_SheetChange(ByVal Sh As Object, ByVal Source As Range)
    Debug.Print "Changed"
    On Error GoTo Finish
    App.EnableEvents = False
    DoWorkOnChangedStuff Sh, Source
Finish:
    App.EnableEvents = True
End Sub

In my Add-In, I've added a line to instantiate the new class:

Public MySheetHandler As New SheetChangeHandler

My understanding is that this should make Excel send the Add-In all SheetChange events for all open workbooks, without those workbooks needing to contain any macro code.

But it's not working... no Debug lines, and my DoWorkOnChangedStuff code isn't being called when I change a cell on any worksheet.

Any ideas?

Answer 1

Don't use the New keyword in the dim statement. You're telling it to instantiate the class when it's needed, but then you never refer to it again, so it's never needed. Instead:

Public MySheetHandler As SheetChangeHandler

Sub Auto_Open
   Set MySheetHandler = New SheetChangeHandler
End Sub

That line in the Auto_Open (which runs at startup) will instantiate the class.