vba 按下“刷新”或“全部刷新”按钮后如何调用宏？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26983053/
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 to call macro after Refresh or Refresh All button pressed?
提问by circld
Ultimately, I would like to run a macro after anyone refreshes the workbook, specifically using the Refresh button under the Data tab in Excel.
最终，我想在任何人刷新工作簿后运行一个宏，特别是使用 Excel 中“数据”选项卡下的“刷新”按钮。
For the time being, I would be satisfied just getting the BeforeRefresh or AfterRefresh QueryTable events to fire upon pressing the Refresh button.
就目前而言，只要在按下刷新按钮时触发 BeforeRefresh 或 AfterRefresh QueryTable 事件，我就会感到满意。
In addition to the documentation on the Microsoft Dev Center website, the relevant posts I have read include:
除了 Microsoft Dev Center 网站上的文档，我阅读的相关帖子还包括：
- Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes
- VBA For Excel AfterRefresh Event
- There are other posts but I lack the reputation to post them.
- Excel VBA - 刷新完成后未调用 QueryTable AfterRefresh 函数
- VBA For Excel AfterRefresh 事件
- 还有其他帖子，但我没有发布它们的声誉。
Here is what I have:
这是我所拥有的：
Under Class Modules (qtclass)
在类模块下 (qtclass)
Option Explicit
Private WithEvents qt As Excel.QueryTable
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "qt_AfterRefresh called sucessfully."
If Success = True Then
Call Module2.SlicePivTbl
MsgBox "If called succesfully."
End If
End Sub
Private Sub qt_BeforeRefresh(Cancel As Boolean)
MsgBox "qt_BeforeRefresh called."
End Sub
Under the ThisWorkbook module
在 ThisWorkbook 模块下
Private Sub Workbook_Open()
Dim qtevent As qtclass
Dim qt As QueryTable
Set qt = ThisWorkbook.Worksheets("Data-Fund").ListObjects(1).QueryTable
Set qtevent = New qtclass
End Sub
I have tried variations of the second code block under specific worksheets as well, but have yet to find anything that works. Do I need to somehow dim the QueryTable in question in the Worksheet module?
我也尝试过特定工作表下第二个代码块的变体，但还没有找到任何有效的方法。我是否需要以某种方式使 Worksheet 模块中的 QueryTable 变暗？
回答by Rory
You haven't actually connected the querytable to the class instance. Revised qtclass
您实际上还没有将查询表连接到类实例。修改了qtclass
Option Explicit
Private WithEvents qt As Excel.QueryTable
Public Property Set HookedTable(q As Excel.QueryTable)
Set qt = q
End Property
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "qt_AfterRefresh called sucessfully."
If Success = True Then
Call Module2.SlicePivTbl
MsgBox "If called succesfully."
End If
End Sub
Private Sub qt_BeforeRefresh(Cancel As Boolean)
MsgBox "qt_BeforeRefresh called."
End Sub
New ThisWorkbook code:
新的此工作簿代码：
Dim qtevent As qtclass
Private Sub Workbook_Open()
Set qtevent = New qtclass
Set qtevent.HookedTable = ThisWorkbook.Worksheets("Data-Fund").ListObjects(1).QueryTable
End Sub
Note that this is quite closely coupled. It would be more re-usable if you were to raise events in the class and declare your qtevent variable WithEvents.
请注意，这是非常紧密的耦合。如果您要在类中引发事件并声明您的 qtevent 变量 WithEvents，它将更加可重用。
