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

[vbaNumpty]

I have the class module:

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

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

 

[vbaNumpty]

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:

Option Explicit

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

    MsgBox "qry is refreshed"

End Sub

The regular module:

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.

 

[vbaNumpty]

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

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

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.

Dim X As New Class1

Sub Initialize_It()

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

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 :

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 !

 

[donisiusalberto]

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?

 

[Domsku]

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

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

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.

Dim X As New Class1

Sub Initialize_It()

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

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 :

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!

 

[RoryA]

Really you should just use Class1 and put the AfterRefresh code in there too.