Trigger a macro on external link update

I need an advice on how to trigger a macro when pressing the update button in external links window (in excel).

As far as I know, there is no such event which would do the job.

I know that there is a possibility to create a custom event, however I have no experience with programming such complex classes, and in addition I'm not even sure if this is possible.

Any suggestions would be highly appreciated.

Hi Bernie,

thanks for the time taken to reply.
Unfortunately this doesn't solve my problem. I need a solution on a workbook level (was testing also the SheetCalculate and SheetChange in the ThisWorkbook event section, but if the refreshed value doesn't change, the event is not triggered).

To be more precise, i need to get somehow the timestamp of the refreshed link. This would be easy, if the link would be refreshed via code, but in my case this is not acceptable.
I'm building an add-in, in which this information would be stored, and then later when the specific functionality (macro in the background) is called by the user, this timestamp would be used as an input parameter.

(hope the explanation was clear )

Yep that would be possible, but these are workarounds.
I still hope I will figure out a nice and neat solution...if there is an event which can be raised by for ex. a sheet change, why wouldn't it be possible to create a custom event which would be triggered by the built-in button.

In general I think that almost everything is possible, well...until its not

So I found a code on the internet & changed it a bit and I think I'm almost there...

The code uses a custom class which raises an event for a specified command.
In the example below i use an ID 21 which refers to the "Cut" command - i have chosen it for testing because the id 21 is the same both for the context menu and the tab menu as well.

It works perfectly when you right click on a cell and press cut (in the context menu), but for some reason I cannot make it work for the Home Tab button.

Any thoughts on what is the issue here?


--> Paste to ThisWorkbook event window:

Private Sub Workbook_Open()
    InitEvents
End Sub

--> Create a Class named Class1 and paste the below:

Public WithEvents cmdCalculate As Office.CommandBarButton

Private Sub cmdCalculate_Click(ByVal cmdCalculate As CommandBarButton, CancelDefault As Boolean)
    MsgBox "Hello"
End Sub

--> Create a normal module and paste the below:

Private clsCBClass As New Class1

Sub InitEvents()
Dim cmdCalculate As CommandBarButton
Set clsCBClass.cmdCalculate = Application.CommandBars.FindControl(ID:=21)
End Sub