Querrytable refresh

[Hellboy]

Hi

I would like to know how and if it's possible to assign an action or a macro to run, when a user refresh a querry. ?

[WillR]

What version of Excel are you running ?

I can check this tomorrow, however I think this can be done in 2003. Don't think so in previous versions (or at least I don't think there existed a specific event you can capture).

[tommy bak]

this can be done from xl97 and upwards and involves the use of a classmodule (This is mainly a direct copy from excel's helpsystem)

Create a classmodule
Put this code into the classmodule

Option Explicit
Public WithEvents qtQueryTable As QueryTable

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

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)
Call test
End Sub

create a normal module and put this code into it

Dim clsQueryTable As New Class1

Sub RunInitQTEvent()
    clsQueryTable.InitQueryEvent _
        QT:=ActiveSheet.QueryTables(1)
End Sub

Sub test()
MsgBox "Querytable is now updated"
End Sub

Before the code will run it's nessecary to run Sub RunInitQTEvent()

[Hellboy]

Thank's tommy bak

the problem i had is that i did not do your last recommandation: "Before the code will run it's nessecary to run Sub RunInitQTEvent()"

What i did is that i used the Workbook_Open module, so it initiate automaticly the routine.

Phil vbmenu_register("postmenu_21028", true);