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?
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.