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
"Dean Hinson" wrote in message
...
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.