Trap message box with VBA Code

Dear Experts:

Can I trap this built-in message somehow so that this built-in message box does not appear at all if a user opens this file that contains linked data.


Msgbox-built-in-file-contains-links.jpg

This is the german version of Excel (2010). The message box says:

'This file contains links to external data sources. If you update the links, Excel will try to update the information. Etc.

The buttons below say: 'Update' and 'Do not Update'

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Have you tried the option to disable security alert?
Roy CoxGroup Finance ManagerCommented:
Try this

Private Sub Workbook_Activate()
   Application.AskToUpdateLinks = False

End Sub

Open in new window

Andreas HermleTeam leaderAuthor Commented:
Dear Saqib,

thank you very much for your swift help. This, regrettably did not have any effect

Dear Roy,
although this seems very logical to me what you are suggesting, it does not work, either

I tried ...

Private Sub Workbook_Open()

    ' Suppress dialog
    Application.DisplayAlerts = False
 
End Sub


And it worked ... but now there are no alerts at all. It is strange that 'Application.AskToUpdateLinks = False' is not working.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Rory ArchibaldCommented:
Do you actually want the links updated or not?
Martin LissOlder than dirtCommented:
I believe the Application.AskToUpdateLinks = False line belongs in Workbook_Open rather than Workbook_Activate.
Roy CoxGroup Finance ManagerCommented:
Well spotted Martin, I was in a rush this morning.
Andreas HermleTeam leaderAuthor Commented:
Hi Martin and Roy,

thank you very much for your swift help. I am afraid to tell you that - even if I put the code into the Workbook_Open event the prompt keeps on appearing, prompting me to update or not.
Andreas HermleTeam leaderAuthor Commented:
Rory, thank you very much for your reply.

As a matter of fact, I do not want the links to get updated
Martin LissOlder than dirtCommented:
Try this

Private Sub Workbook_Open()
    Application.DisplayAlerts = False
    Application.AskToUpdateLinks = False  

    ' Existing code if any

    Application.DisplayAlerts = True
End Sub

Open in new window

Roy CoxGroup Finance ManagerCommented:
You won't be able to stop links updating unless you open the workbook using code from another workbook, e.g.

Option Explicit

Private Sub Workbook_Open()
Dim MyFile As String
MyFile = "C:\your file's path\YourFileName.xlsx"
Workbooks.Open Filename:=strFileName, UpdateLinks:=0
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Does it help your situation if before you close the workbook you go to Data tab->Edit Links->Startup Prompt and choose the "Don't display the alert and update links" option?
Andreas HermleTeam leaderAuthor Commented:
Dear all, thank you very much for your great support. I really appreciate it :-)
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
Andreas HermleTeam leaderAuthor Commented:
okay, great, Martin, I will have a look at them shortly.

Regards, Andreas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.