Excel 2019

Worksheet TableUpdate Event

The TableUpdate event occurs after a query table connected to a data model is updated. Excel uses the Worksheet_TableUpdate event procedure to trap the TableUpdate event. The Worksheet_TableUpdate procedure accepts Target (the TableObject) as the parameter which represents the table that was updated.

Creating a data model

You can make a data model by retrieving the data from a file, web, table, database or from any other source listed in the Data > Get Data as shown in the following image:

Get data menu from the Data tab

In our example, we’ll retrieve the data from a file. Create a text file and insert some data into it. For example, I have entered the following data into a blank text file and saved it as data.txt using notepad:

a,b,c
1,5,18
10,12,16
12,15,1
1,2,10

Back to Excel, Click Data tab and select From Text/CSV from the Get & Transform Data section to import the data from the data.txt file:

Import data from text or csv

Select data.txt file:

Select file to import

The next window shows the preview of data in a table form. Click Load To... button from the window as shown below image:

Data preview

Select the Add this data to the Data Model and click ok:

Another dialog box to create data model

Data will show on a new Worksheet and a query (data) will also made which visible on the left side of the sheet under the Queries & Connection:

Queries and Connections

If the data in the data.txt file changes, the changes will not be automatically updated on the table. To update the changes click Data tab and click Refresh All menu from the Queries & Connections section:

Click Refresh All to update data

Worksheet_TableUpdate

The TableUpdate event occurs after a query table connected to a data model is updated. The Worksheet_TableUpdate procedures must be in the code module for that worksheet. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

View code context menu

In our data-model, the data imported to new sheet, Sheet2. Right-click on the Sheet2 tab, select View Code and write the following code.

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
 MsgBox "Data from the data.txt has been updated"
End Sub

Back to Sheet2 and click Data tab and click Refresh All menu from the Queries & Connections section, it will reload (update) the data from the data.txt file and Excel fires the TableUpdate event which executes the Worksheet_TableUpdate event procedure:

The TableUpdate procedure executed

Advertisement:
Advertisement: