Yeah, if you don't want to add a custom button on the Ribbon, a custom class would be an alternative. Actually, I mentioned it earlier in Post #22 of this thread. In any case, try the following code., test it throroughly, and let me know if there are any issues.
Note that the
AfterRefresh event gets triggered every time a table is refreshed via
RefreshAll or
Refresh. The code keeps track of which table gets refreshed. When all tables are refreshed, your code runs, and then the process of keeping track starts over again.
Also, note that the code contains a number of debug statements. These statements are included so that you can see exactly what happens. After clicking on RefreshAll or Refresh, have a look at the Immediate Window (Ctrl+G) for the results. Of course, these statements can be removed once you've fully tested the code.
First insert a class module (Visual Basic Editor >> Insert >> Class Module), and then in the Properties Window (F4) under Name, rename the module
clsQueryTable. Then place the following code in the code module...
VBA Code:
Option Explicit
Private WithEvents m_queryTable As QueryTable
Public Property Set QueryTable(ByVal qt As QueryTable)
Set m_queryTable = qt
End Property
Private Sub m_queryTable_AfterRefresh(ByVal Success As Boolean)
If Success Then
Debug.Print "AfterRefresh: " & m_queryTable.ListObject.Name
Dim tableName As String
tableName = m_queryTable.ListObject.Name
dicQueryTables(tableName)("refreshed") = 1
myMacro 'calls your macro, change the name accordingly
End If
End Sub
Then insert a regular module (Visual Basic Editor >> Insert >> Module), and place the following code in the code module...
VBA Code:
Option Explicit
Public dicQueryTables As Object
Public Sub init(Optional ByVal dummy As Long)
Debug.Print "init . . ."
Set dicQueryTables = CreateObject("Scripting.Dictionary")
Dim ws As Worksheet
Dim oListObject As ListObject
Dim oQueryTable As QueryTable
Dim cQueryTable As clsQueryTable
Dim dicTableTracker As Object
For Each ws In ThisWorkbook.Worksheets
For Each oListObject In ws.ListObjects
On Error Resume Next
Set oQueryTable = oListObject.QueryTable
On Error GoTo 0
If Not oQueryTable Is Nothing Then
Set cQueryTable = New clsQueryTable
Set cQueryTable.QueryTable = oQueryTable
Set dicTableTracker = CreateObject("Scripting.Dictionary")
dicTableTracker.Add key:="query_table", Item:=cQueryTable
dicTableTracker.Add key:="refreshed", Item:=0
dicQueryTables.Add key:=oListObject.Name, Item:=dicTableTracker
Set oQueryTable = Nothing
Set dicTableTracker = Nothing
End If
Next oListObject
Next ws
End Sub
Public Sub myMacro(Optional ByVal dummy As Long)
Debug.Print "myMacro . . ."
If dicQueryTables.Count > getRefreshCount() Then
Debug.Print "Exit myMacro . . ."
Exit Sub
End If
Debug.Print "Your code runs . . ."
'Your code goes here
'
'
resetTableTracking
End Sub
Public Function getRefreshCount() As Long
Debug.Print "getRefreshCount . . ."
Dim refreshCount As Long
refreshCount = 0
Dim key As Variant
For Each key In dicQueryTables.keys()
If dicQueryTables(key)("refreshed") = 1 Then
refreshCount = refreshCount + 1
End If
Next key
getRefreshCount = refreshCount
End Function
Public Sub resetTableTracking()
Debug.Print "resetTableTracking . . ."
Dim key As Variant
For Each key In dicQueryTables.keys()
dicQueryTables(key)("refreshed") = 0
Next key
End Sub
Then, in the Project Explorer window (Ctrl+R), right-click ThisWorkbook, select View Code, and then place the following code in the code module...
VBA Code:
Option Explicit
Private Sub Workbook_Open()
init
End Sub
