I need help with QueryTable Events (AfterRefresh)

[Dean Hinson]

I have searched discussions groups and googled til I can't take it no more.
I have been trying to get the qtQueryTable_AfterRefresh with no success.
Here is my code....

ThisWorkbook contains the following:

Option Explicit
Dim QT As ClsModQT
Dim wsQuery As Worksheet
Private Sub Workbook_Open()

Set QT = New ClsModQT
Set wsQuery = Sheets("HD")
QT.InitQueryEvent wsQuery.QueryTables(1)

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.ThisWorkbook.RefreshAll
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Class Module ClsModQT contains the following....

Sub InitQueryEvent(QT As Object)
Set qtQueryTable = QT
End Sub

Private Sub Class_Initialize()

End Sub

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)

Dim wsCurrent As Worksheet
Dim rngUsed As Range
Dim strFormula As String

' Refresh Age Column on HD Worksheet

Set wsCurrent = Application.Worksheets("HD")
Set rngUsed = GetUsedRange(wsCurrent)

For r = 2 To rngUsed.Rows.Count
strFormula = "=NOW()-G" & r
rngUsed.Cells(r, 8).FormulaR1C1 = strFormula
Next r

' Refresh Age Column on CHG Worksheet

Set wsCurrent = Application.Worksheets("CHG")
Set rngUsed = GetUsedRange(wsCurrent)

For r = 2 To rngUsed.Rows.Count
strFormula = "=NOW()-G" & r
rngUsed.Cells(r, 8).FormulaArray = strFormula
Next r

Set wsCurrent = Nothing
Set rngUsed = Nothing

End Sub

The afterrefresh never fires. What am I doing wrong?

Thank you in advance for any assistance.

Regards, Dean.

 

[Rob Bovey]

Hi Dean,

In your class module code you don't show the declaration for the
qtQueryTable variable. It should be in the general declaration section at
the top of the class module and look something like this:

Private WithEvents qtQueryTable As Excel.QueryTable

The key feature of the declaration being the inclusion and proper location
of the WithEvents keyword.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[K Dales]

One important item you omitted: Where and how do you Dim your qtQueryTable
variable? It needs to be defined WithEvents in order to use the event
procedures - can't tell whether you did this or not.