Can't get my afterrefresh or beforerefresh to work correctly.

vbaNumpty

Board Regular
Joined
Apr 20, 2021
Messages
171
Office Version
  1. 365
Platform
  1. Windows
I have the class module:

VBA Code:
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

The initialize module

VBA Code:
Sub InitializeQueries()

    Dim clsQ As clsQuery
    Dim WS As Worksheet
    Dim QT As QueryTable
    Dim LO As ListObject
    
    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
        
    For Each LO In WS.ListObjects
    
        Set QT = LO.QueryTable
        Set clsQ = New clsQuery
        Set clsQ.MyQuery = QT
        colQueries.Add clsQ
        
        Next LO
        
    Next WS

End Sub

I get an error on

Set QT = LO.QueryTable,

If I remove the LO portion of the code and just do the For Each QT, it loops through without ever getting into the set, it goes to next QT each loop.

Ideally I only want the before and after refresh events to apply to the query table on sheet3. The query is a range from another workbook. This is the last thing holding back my project from completion and will eternally be grateful to whomever can guide me to getting this to work. Thanks
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
I have changed things and have the code "working" from a thread I found on here from 2013, but my afterrefresh event doesn't seem to be working because no message box appears.

The class module:

VBA Code:
Option Explicit

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qry is refreshed"

End Sub

The regular module:
VBA Code:
Sub Initialize_It()

  Dim X As clsQuery

  Set X = New clsQuery
  Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable
  
End Sub

When I click refresh on the ribbon nothing happens.
 
Upvote 0
For anyone who like me bashed their heads against the wall trying to figure this out, if you happen across this post - I GOT YOU! Also conveniently includes a before refresh as well.

Found on another forums after a long search, I will copy and past the answer here because as I frustratingly have found out - links can die

Also want to point out if your query isn't a list object as mine was, you will need to change the following:

Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable

to Set X.qt = ThisWorkbook.Sheets("Orders").QueryTables(1)

Don't forget to make the sheet name match yours!

Link: Here

Code:
Class1 (Module Name) - these have to be class modules
VBA Code:
Public WithEvents qt As QueryTable

Private Sub qt_BeforeRefresh(Cancel As Boolean)

    Dim a As Integer
    Dim my_Prompt As String
    
    my_Prompt = "Please wait while data refreshes"
    a = MsgBox("Do you want to refresh the data now?", vbYesNoCancel)
        If a = vbNo Then
            my_Prompt = "Data will not be refreshed."
            Cancel = True
        End If
    MsgBox my_Prompt
End Sub

Class2 (Module Name) - these have to be class modules
VBA Code:
Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "Data has been refreshed"
    
End Sub

Regular Modules: (Names are Module4 and Module5 - I use the same sub name so it's important to remember that when viewing the initialize code.

VBA Code:
Dim X As New Class1

Sub Initialize_It()

    Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable
    
End Sub


VBA Code:
Dim X As New Class1

Sub Initialize_It()

    Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable
    
End Sub

Then in the workbook open just put :

VBA Code:
Module4.Initialize_It
Module5.Initialize_It

Remember to change the module names to match yours, or you can just give the different subs unique names. Pick your poison.

Hope this helps someone !
 
Upvote 1
Solution
Hello, I'm wondering about which macro or sub should to be placed onto the button to run the macro. The private sub and class module could not be assigned anywhere, while the module 4&5 is not the proper macro. Could you please elaborate more information?
 
Upvote 0
For anyone who like me bashed their heads against the wall trying to figure this out, if you happen across this post - I GOT YOU! Also conveniently includes a before refresh as well.

Found on another forums after a long search, I will copy and past the answer here because as I frustratingly have found out - links can die

Also want to point out if your query isn't a list object as mine was, you will need to change the following:

Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable

to Set X.qt = ThisWorkbook.Sheets("Orders").QueryTables(1)

Don't forget to make the sheet name match yours!

Link: Here

Code:
Class1 (Module Name) - these have to be class modules
VBA Code:
Public WithEvents qt As QueryTable

Private Sub qt_BeforeRefresh(Cancel As Boolean)

    Dim a As Integer
    Dim my_Prompt As String
   
    my_Prompt = "Please wait while data refreshes"
    a = MsgBox("Do you want to refresh the data now?", vbYesNoCancel)
        If a = vbNo Then
            my_Prompt = "Data will not be refreshed."
            Cancel = True
        End If
    MsgBox my_Prompt
End Sub

Class2 (Module Name) - these have to be class modules
VBA Code:
Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "Data has been refreshed"
   
End Sub

Regular Modules: (Names are Module4 and Module5 - I use the same sub name so it's important to remember that when viewing the initialize code.

VBA Code:
Dim X As New Class1

Sub Initialize_It()

    Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable
   
End Sub


VBA Code:
Dim X As New Class1

Sub Initialize_It()

    Set X.qt = ThisWorkbook.Sheets("Orders").ListObjects("OrderData").QueryTable
   
End Sub

Then in the workbook open just put :

VBA Code:
Module4.Initialize_It
Module5.Initialize_It

Remember to change the module names to match yours, or you can just give the different subs unique names. Pick your poison.

Hope this helps someone !
Hello! I made an account on MrExcel especially to thank you!! I've been searching for this solution for so long. Did notice one little mistake though: In module5 you need to specify "Class2" instead of "Class 1". In your snippet, both modules have same code :) But apart from that great stuff thanks!
 
Upvote 0
Really you should just use Class1 and put the AfterRefresh code in there too.
 
Upvote 1

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top