How can an Excel Add-In respond to events in any worksheet?
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 SheetChangeHandlerSub Auto_Open Set MySheetHandler = New SheetChangeHandlerEnd Sub
That line in the Auto_Open (which runs at startup) will instantiate the class.
Got info from:http://www.bettersolutions.com/vba/events/creating-application-level.htmand tried Dick Kusleika's solution but did not get the class module working.After 2 days of web searching and before give-up I tried this and worked for my.Considering : "workbooks are server-generated SpreadsheetML, which cannot include any VBA code", also my requirements.
I wrote this in my class module named "ApplicationEventClass":
Option ExplicitPublic WithEvents ExcelAppEvents As ApplicationPrivate Sub Class_Initialize() Set ApplicationClass.ExcelAppEvents = ApplicationEnd SubPrivate Sub ExcelAppEvents_SheetChange(ByVal Sh As Object, ByVal Target As Range)Target.Font.ColorIndex = 5End Sub
And this in my module named "Module1":
Option ExplicitPublic ApplicationClass As New ApplicationEventClassSub ConnectEventHandler() On Error Resume Next Set ApplicationClass.ExcelAppEvents = ApplicationEnd Sub
Thats it! I hope this work for you too.Obviously only change the text color to blue in any worksheet.