Using Events with the QueryTable Object

Before you can use events with the QueryTable object, you must first create a new class module and declare a QueryTable object with events. For example, assume that you’ve created a new class module and named it ClsModQT. This module contains the following code:

Public WithEvents qtQueryTable As QueryTable

After you’ve declared the new object by using events, it appears in the Object drop-down list box in the class module.

Before the procedures will run, however, you must connect the declared object in the class module to the specified QueryTable object. You can do this by entering the following code in the class module:

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

After you run this initialization procedure, the object you declared in the class module points to the specified QueryTable object. You can initialize the event in a module by calling the event. In this example, the first query table on the active worksheet is connected to the qtQueryTable object.

Dim clsQueryTable as New ClsModQT

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

You can write other event procedures in the object’s class. When you click the new object in the Object box, the valid events for that object are displayed in the Procedure drop-down list box.