Example Usage: Worksheet.TableUpdate Event ?

Hi Folks,

I'm struggling a bit with this event.

What I want to do is run some code on a specific  column ("NAEMAL") that contains a textual e-mail address whenever the connection is refreshed and the data is updated.(Hopefully this is the Worksheet.TableUpdate Event???)

The table "tblVendors" is populated by one of Excel 2016's new queries, and every time the query runs, it returns a text value. :(

The idea here is to convert that textual e-mail address  to a hyperlink ... automatically.

MSDN: https://msdn.microsoft.com/en-us/library/office/jj229788.aspx talks about this event, but does not provide an example.

Here is the (tested and working) code that I want to run when the data is refreshed:

============================================

  

Sub ConvertEmailtoHyperlink()
Dim lstObj As ListObject
Dim rColumn As Range
Dim i As Integer
Dim txtValue As String
' Get the table reference
Set lstObj = Worksheets("VendorQuery").ListObjects("tblVendors")

' Get the column reference

Set rColumn = lstObj.ListColumns("NAEMAL").DataBodyRange

With rColumn

    For i = 1 To .Rows.Count
        txtValue = .Rows(i).Value
        If InStr(1, txtValue, "@") > 1 Then
            'Debug.Print txtValue

             .Hyperlinks.Add Anchor:=rColumn.Rows(i), _
             Address:="mailto:" & txtValue, _
             TextToDisplay:=txtValue
        End If
    Next i
End With
End Sub
====================
TIA,

Don

Assuming that your existing code is in a standard module then in the Table Update event try inserting the following line of code.

Call ConvertEmailtoHyperlink

If your existing code is in a module other than a standard module then the line of code will be as follows.

Call  ModuleName.ConvertEmailtoHyperlink           'Replace ModuleName with the name of the module

Regards,

OssieMac

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Thanks, but I did know how to call my function from within the event.

The function is now in a module named 'modConvertEmail

My issue is that I can't seem to get the event to fire at all.

Occurs after a Query table connected to the Data Model is updated on a worksheet.

expression .TableUpdate(Target)

expression A variable that represents a Worksheet object.

Here is the worksheet code in it's entirety:

============================

Option Explicit

Private Sub cmdConvertEmail_Click()
Call ConvertEmailtoHyperlink 'This works fine
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ("This is the SelectionChange event") 'This works, too.
End Sub

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
MsgBox ("This is the TableUpdate event")' Nothing happens here
Call ConvertEmailtoHyperlink

End Sub

================================

As an experiment, I purposely deleted approximately 20 rows, and then clicked on "Refresh All"

The rows do get added back in ... which tells me that the refresh is occurring, yet the event does not appear to 'fire'.

*scratches head*

-- Finally allowed to use Access at work! So excited! --

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Is this a table as created by Insert -> Table? Your code line "Set lstObj = Worksheets("VendorQuery").ListObjects("tblVendors")" suggests that it is.

If above is correct then wrong type of table. It needs to be a Query table connected to the Data Model. Represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database.

See the following link and also check out the links within that web page.

https://msdn.microsoft.com/en-us/library/office/ff198271(v=office.15).aspx

Regards,

OssieMac

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Is this a table as created by Insert -> Table? Your code line "Set lstObj = Worksheets("VendorQuery").ListObjects("tblVendors")" suggests that it is.

If above is correct then wrong type of table. It needs to be a Query table connected to the Data Model. Represents a worksheet table built from data returned from an external data source, such as an SQL server or a Microsoft Access database.

See the following link and also check out the links within that web page.

https://msdn.microsoft.com/en-us/library/office/ff198271(v=office.15).aspx

No, the table was created using Excel 2016's "New Query " (Which I think is the same as what they introduced in Excel 2013 as a "PowerQuery" add-on)

Getting Started with Get & Transform in Excel 2016

It's a long story, but our business software runs on an AS400 "i-Series", and the IBM software "IBM System i Access for Windows" allows me to download to an XLS file (not XLSX)

1.) Download to XLS.

2.) Use this query to update the XLSX file so that the data can be "massaged" a bit (i.e format phone numbers and e-mail links, etc)

3.) Link the XLSX table to an Access Hybrid (Desktop/Sharepoint web app)

Yes, I know it's convoluted ... and I'll likely (eventually) establish an ODBC connection directly to Access.

But that's a whole 'nuther story. You see ... my IT guy refuses to let me install Access on my work PC, or even to let me bring my own laptop and connect to the network. "Use Excel, it can do VBA" Ugh.

-- Finally allowed to use Access at work! So excited! --

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Sorry that I can't help further. I don't think that I can suggest anything else at this point in time.
Regards,

OssieMac

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated January 8, 2021 Views 5,414 Applies to: