Excel VBA - Don't show "Enable Links" dialog

Posted on 2011-10-31
Last Modified: 2012-05-12
Is there a way to stop the dialog that asks a user whether or not to enable links?

Thanks, --Andres
Question by:AndresHernando
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Go to "Edit links" and from there select "Startup prompt" and then select the preferred option
    LVL 35

    Expert Comment

    by:Robert Schutt
    Or, if you're already using VBA to open a workbook and want to avoid the dialog in that situation, look at the arguments of Workbooks.Open: you can decide to update links or not.

     options for UpdateLinks argument
    LVL 6

    Expert Comment

    Edit links should be in the data tab.

    Here we will provide two methods to find out the Edit links command in Microsoft Excel 2010 and Excel 2007.

    Author Comment

    I should have been more explicit in my question (sorry).
    I want to email a file (filename.xlsm) that will NOT ask to enable links.  The file will keep links disabled.

    Is there a way to include code in the file that will do this?
    LVL 35

    Accepted Solution

    Maybe you want to break the links and only leave the values?

    This can be done manually (in my version of excel via the ribbon menu: Data - Edit links - Break link) or with VBA:

    Private Sub unlinkall()
        Dim ls
        If Not IsEmpty(ActiveWorkbook.LinkSources) Then
            For Each ls In ActiveWorkbook.LinkSources(xlExcelLinks)
                ActiveWorkbook.BreakLink ls, xlLinkTypeExcelLinks
        End If
    End Sub

    Open in new window

    Then save as a copy and email that version.

    Author Comment

    I just want to avoid the dialog from opening at all.  I want to simplify to avoid the recipient of the file having to figure out what to do (which is to NOT enable links).
    LVL 35

    Expert Comment

    by:Robert Schutt
    exactly, so if he's never going to be able to update the links you could break the link before mailing the file. I'm not sure if it's possible to prevent the message otherwise.

    Author Comment

    Robert, Ah, of course!...  that's exactly what I needed.   Thanks!  
    ssaqibh and roy, thanks for your input.  --Andres

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now