Returning a web query can be very handy. Unfortunately if you want to run any code when this refreshes, there’s no native way to do this. We can, however, create an event for this with a few short steps.

This will all be done inside the Visual Basic Editor (VBE). To get there click Visual Basic on the Developer tab (pictured below) or hit ALT + F11.

Step 1: Create a class module

In the VBE, right-click your project and select Insert

Class Module (pictured below). You can also do this from the Insert menu on the command bar.

Step 2: Rename class module

This is a very important step and is probably the most missed step when people re-create class module solutions.

For this example we will rename it (select the class module, press F4) to “clsQuery“.

The class name is referred to in code later on in this post. If you name it something different, be sure to adjust your code as well.

Paste the following code into the class module (double-click it in the PE to see the code pane):

[sourcecode language=”vb”]Option Explicit

Public WithEvents MyQuery As QueryTable

Private Sub MyQuery_AfterRefresh(ByVal Success As Boolean)
If Success Then MsgBox “Query has been refreshed.”
End Sub

Private Sub MyQuery_BeforeRefresh(Cancel As Boolean)
If MsgBox(“Refresh query?”, vbYesNo) = vbNo Then Cancel = True
End Sub[/sourcecode]

Your code should now look like the following:

Step 3: Create the standard module

Right-click your project in the PE and select Insert

Module.

Paste the following code (updated on 10-Feb-14, thanks to Andy Pope for pointing out a flaw!):

[sourcecode language=”vb”]Option Explicit

Dim colQueries As New Collection

Sub InitializeQueries()

Dim clsQ As clsQuery
Dim WS As Worksheet
Dim QT As QueryTable

For Each WS In ThisWorkbook.Worksheets
For Each QT In WS.QueryTables
Set clsQ = New clsQuery
Set clsQ.MyQuery = QT
colQueries.Add clsQ
Next QT
Next WS

End Sub[/sourcecode]

To initialize this code when the workbook opens, double click ThisWorkbook module and use the following workbook open code:

[sourcecode language=”vb”]Option Explicit

Private Sub Workbook_Open()
Call InitializeQueries
End Sub[/sourcecode]

That’s it, you now have query before/after events you can tie into. Note that if you make changes to the class module you’ll have to run the initialize code again.