PDA

View Full Version : [SOLVED] Querrytable refresh



Hellboy
03-21-2005, 12:52 PM
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
03-27-2005, 10:28 AM
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
03-27-2005, 11:15 AM
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
03-28-2005, 05:43 AM
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);