vba Excel 加载项如何响应任何工作表中的事件？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/858691/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can an Excel Add-In respond to events in any worksheet?
提问by richardtallent
Our workbooks are server-generated SpreadsheetML, which cannot include anyVBA code. Creating native Excel files or Excel 2007 XML files is also not an option, unfortunately.
我们的工作簿是服务器生成的 SpreadsheetML，其中不能包含任何VBA 代码。不幸的是，创建本机 Excel 文件或 Excel 2007 XML 文件也不是一种选择。
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.
所以，我有一个 Excel 插件（VBA，而不是 XLL），我们的每个用户都会安装它来添加我们工作簿需要的一些额外的 UDF 等。
This works great, but now I need to have a macro that needs to execute every timethe 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:
到目前为止，我已经在我的加载项中创建了一个新的类模块 (SheetChangeHandler)，代码如下：
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 allSheetChange events for all open workbooks, without those workbooks needing to contain any macro code.
我的理解是，这应该使 Excel为所有打开的工作簿发送 Add-In allSheetChange 事件，而这些工作簿不需要包含任何宏代码。
But it's not working... no Debug lines, and my DoWorkOnChangedStuff code isn't being called when I change a cell on any worksheet.
但它不起作用......没有调试行，并且当我更改任何工作表上的单元格时，不会调用我的 DoWorkOnChangedStuff 代码。
Any ideas?
有任何想法吗？
采纳答案by Dick Kusleika
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:
不要在dim 语句中使用New 关键字。您告诉它在需要时实例化类，但是您再也不会引用它，因此永远不需要它。反而：
Public MySheetHandler As SheetChangeHandler
Sub Auto_Open
Set MySheetHandler = New SheetChangeHandler
End SubThat line in the Auto_Open (which runs at startup) will instantiate the class.
Auto_Open（在启动时运行）中的那一行将实例化该类。
回答by Jorge Jaime
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.
从以下位置获取信息：http: //www.bettersolutions.com/vba/events/creating-application-level.htm并尝试了 Dick Kusleika 的解决方案，但没有使类模块正常工作。经过 2 天的网络搜索和放弃之前，我尝试了这个并对我来说有效。考虑到：“工作簿是服务器生成的 SpreadsheetML，不能包含任何 VBA 代码”，这也是我的要求。
I wrote this in my class module named "ApplicationEventClass":
我在名为“ApplicationEventClass”的类模块中写了这个：
Option Explicit
Public WithEvents ExcelAppEvents As Application
Private Sub Class_Initialize()
Set ApplicationClass.ExcelAppEvents = Application
End Sub
Private Sub ExcelAppEvents_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Target.Font.ColorIndex = 5
End Sub
And this in my module named "Module1":
这在我名为“Module1”的模块中：
Option Explicit
Public ApplicationClass As New ApplicationEventClass
Sub ConnectEventHandler()
On Error Resume Next
Set ApplicationClass.ExcelAppEvents = Application
End Sub
Thats it! I hope this work for you too. Obviously only change the text color to blue in any worksheet.
就是这样！我希望这对你也有用。显然，只能在任何工作表中将文本颜色更改为蓝色。
