You will likely need to create a reference to your query table and use the events sourced with this object. Download the working example.
QueryTableEvents.zip
This code must go into a class module such as a worksheet, workbook, or custom class. This code is located in sheet1 of the example.
This first example shows you how to reference the sourced events of a querytable that is created on the fly or dynamically...
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Private</font> <font color="#0000A0">WithEvents</font> qt <font color="#0000A0">As</font> QueryTable
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton1_Click()
<font color="#0000A0">Set</font> qt = ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://home.fuse.net/tstom/Book1.html", Destination:=Range("A1"))
<font color="#0000A0">With</font> qt
.Name = "Book1"
.FieldNames = <font color="#0000A0">True</font>
.RowNumbers = <font color="#0000A0">False</font>
.FillAdjacentFormulas = <font color="#0000A0">False</font>
.PreserveFormatting = <font color="#0000A0">True</font>
.RefreshOnFileOpen = <font color="#0000A0">False</font>
.BackgroundQuery = <font color="#0000A0">True</font>
.RefreshStyle = xlInsertDeleteCells
.SavePassword = <font color="#0000A0">False</font>
.SaveData = <font color="#0000A0">False</font>
.AdjustColumnWidth = <font color="#0000A0">True</font>
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = <font color="#0000A0">True</font>
.WebConsecutiveDelimitersAsOne = <font color="#0000A0">True</font>
.WebSingleBlockTextImport = <font color="#0000A0">False</font>
.WebDisableDateRecognition = <font color="#0000A0">False</font>
.WebDisableRedirections = <font color="#0000A0">False</font>
.Refresh BackgroundQuery:=False
<font color="#0000A0">End</font> <font color="#0000A0">With</font>
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> qt_BeforeRefresh(Cancel <font color="#0000A0">As</font> Boolean)
MsgBox "The data is now going to be refreshed..."
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> qt_AfterRefresh(ByVal Success <font color="#0000A0">As</font> Boolean)
MsgBox "Your data has been refreshed. This is where you would run your respondent code..."
Me.Names(qt.Name).Delete
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("9232006121121906").value=document.all("9232006121121906").value.replace(/<br \/>\s\s/g,"");document.all("9232006121121906").value=document.all("9232006121121906").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("9232006121121906").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="9232006121121906" wrap="virtual">
Private WithEvents qt As QueryTable
Private Sub CommandButton1_Click()
Set qt = ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://home.fuse.net/tstom/Book1.html", Destination:=Range("A1"))
With qt
.Name = "Book1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End Sub
Private Sub qt_BeforeRefresh(Cancel As Boolean)
MsgBox "The data is now going to be refreshed..."
End Sub
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "Your data has been refreshed. This is where you would run your respondent code..."
Me.Names(qt.Name).Delete
End Sub
</textarea>
This second example does the exact same thing as above except we are referencing an existing querytable. The code for this example is located in sheet2 in the example download.
<table width="100%" border="1" bgcolor="White" style="filter
rogid:DXImageTransform.Microsoft.Gradient(endColorstr='#C0CFE2', startColorstr='#FFFFFF', gradientType='0');"><tr><TD><font size="2" face=Courier New> <font color="#0000A0">Private</font> <font color="#0000A0">WithEvents</font> qt <font color="#0000A0">As</font> QueryTable
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> CommandButton1_Click()
<font color="#0000A0">Set</font> qt = ActiveSheet.QueryTables("Book1")
qt.Refresh
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> qt_BeforeRefresh(Cancel <font color="#0000A0">As</font> Boolean)
MsgBox "The data is now going to be refreshed..."
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
<font color="#0000A0">Private</font> <font color="#0000A0">Sub</font> qt_AfterRefresh(ByVal Success <font color="#0000A0">As</font> Boolean)
MsgBox "Your data has been refreshed. This is where you would run your respondent code..."
<font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table><button onclick='document.all("923200612148843").value=document.all("923200612148843").value.replace(/<br \/>\s\s/g,"");document.all("923200612148843").value=document.all("923200612148843").value.replace(/<br \/>/g,"");window.clipboardData.setData("Text",document.all("923200612148843").value);'>Copy to Clipboard</BUTTON><textarea style="position:absolute;visibility:hidden" name="923200612148843" wrap="virtual">
Private WithEvents qt As QueryTable
Private Sub CommandButton1_Click()
Set qt = ActiveSheet.QueryTables("Book1")
qt.Refresh
End Sub
Private Sub qt_BeforeRefresh(Cancel As Boolean)
MsgBox "The data is now going to be refreshed..."
End Sub
Private Sub qt_AfterRefresh(ByVal Success As Boolean)
MsgBox "Your data has been refreshed. This is where you would run your respondent code..."
End Sub</textarea>
QueryTableEvents.zip