How to stop users adding links to other workbooks

[alanmccarthy]

Hi,

We use Excel for preparing budget files. These files then get collated in MS Power BI for group wide analysis.

One issue we have is that users can sometimes paste links from spreadsheets on their desktops into the budget files. This naturally causes problems when other users open them.

Is there any way to prevent external links from being inserted? I've searched in Data Validation and Protect Sheet but haven't found anything suitable.

[#2]

Stop Automatic Hyperlinks in Excel

How to stop automatic hyperlinks in Excel?

To turn the hyperlink option off in Excel

On the Tools menu, choose AutoCorrect Options
Select the AutoFormat as you type tab
Remove the check mark from Internet and network paths with hyperlinks
Click OK

Remove a Hyperlink Manually

If you want to leave the hyperlink feature on, you can undo the hyperlink immediately after it's created.

To manually remove the hyperlink:

Type the email address and press Enter
Immediately, press Ctrl+Z.

This is a shortcut for Undo, and will convert the hyperlink back to text.
Remove Selected Hyperlinks Programmatically

To change a group of cells that contain hyperlinks, you can use the following code. It deletes all the hyperlinks in the selected cells.

Sub delHyperlinks()
Dim myCell As Range
For Each myCell In Selection
myCell.Hyperlinks.Delete
Next myCell
End Sub

I would be happy to find out if I could help.

Nikolino
I know I don't know anything (Socrates)

[#3]

@NikolinoDE , the issue isn't hyperlinks as such, its cells pointing at external workbooks. So cells with values like the below

='[Desktop Budget File]Project Budget'!$I$23

or

=C://users/userA/desktop/'[Desktop Budget File]Project Budget'!$I$23

[#4]

im not sure but if you make a modul,
insert this code.

Option Explicit
Sub DeleteHyperlinks()
Worksheets("Text").Hyperlinks.Delete
End Sub


Please see this link too, maybe helps:
https://support.microsoft.com/en-gb/office/break-a-link-to-an-external-reference-in-excel-f1ca8b08-4...

[#5]

I am having this same issue.  We use a standard template that users download and complete.  The file has embedded logic, data validations and defined pivots.  Many users are are saving / copying the data into the template that results in data corruptions, link issues and content enablement warning.  The handling of the file seems to pickup every storage location along the way.  This is degrading the effectiveness of this tool.  

This issue is not Hyper Links but the embedded references to name ranges that get corrupted.  

How can this be prevented?

@alanmccarthy 

[#6]

@alanmccarthy 

Got another way to stop hyperlinks, found on the internet.

Break all external links with VBA code

If you want to break all of the external links, both the ok and error links, the following VBA code can help you to break them all.

1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

Sub BreakLinks()
'Updateby20140318
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
    For Each link In wb.LinkSources(xlExcelLinks)
        wb.BreakLink link, xlLinkTypeExcelLinks
    Next link
End If
End Sub

Then press F5 key to run the code, and all of the external links in the whole workbook have been broken.

Hope I was able to help you with this info.

Nikolino

I know I don't know anything (Socrates)

[#7]

I've run into same issue.
The problem is not breaking external links. I just wanna prevent my users from using external links.
Users from overseas use Excel budget files (stored in sharepoint) for providing the headoffice with budget monthly figures. I consolidate data coming from these excel files with PowerQuery (Excel or PowerBI).
I just wanna prevent users from using external links to other excel files stored in their pc. I want them to have to type figures. When I receive those files they already contain many "#REF!", so breaking links doesn't help ( they should break external links before sharing the files , but they forget to do that).
When users enter figures by using external links to their excel files I receive many "#REF!" cell which generate an error when I import this files with PowerQuery in another consolidation file or in PowerBI.
How to prevent them from using external links ?

[#8]

@mamo72 

Block or unblock external content in Office documents

To help protect your security and privacy, Microsoft Office is configured by default to block external content—such as images, linked media, hyperlinks, and data connections—in workbooks and presentations. Blocking external content helps to prevent Web beacons and other intrusive methods that hackers use to invade your privacy and lure you into running malicious code without your knowledge or consent.

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#9]

@NikolinoDE 

Question: The problem is not breaking external links. I just wanna prevent my users from using external links.

You could also do it with the Protection Worksheet.
First:
First you have to select the cells with the hyperlinks,
then right mouse button, format cells, click on Protection above, clicked the Locked box, OK.

After that:
By going to Review, protect sheet. New window opens. Protect worksheet and contents of locked cells check the box.
Select locked cells, check box Empty.

So nobody can press on the hyperlink, see yes, press on it no. At least that's how it is in Excel 2016.

Additional Info:

Description of link management

Thank you for you all understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#10]

@NikolinoDE 

As per original query. We are trying to PREVENT links to other workbooks to OCCUR to begin with 

NOT TRYING TO

– fix links on a document

– Remove links

– Manage some areas or cells of a document.

– Find procedure for a single operation 

WE WANT To

- Find the GLOBAL setting that that prevents copying data from one workbook to another and creating a link to the ordinal document when pasting to another. ALWAYS.

In years of using Excel, the "link to other spreadsheet feature" has never been useful. I understand how that could be nice, but it's the exception, not the what's needed by default. Surely there is a way to just turn it OFF somewhere?.

Where?

Thanks.

[#11]

@Arkinien 

So far, I can understand, you are seeking a way to prevent Excel from automatically creating links to other workbooks when copying data from one workbook to another. This feature can indeed be inconvenient in certain situations.

To prevent Excel from creating links to other workbooks by default, you can adjust the settings in Excel to disable this behavior.

Here is how you can do it:

1. Open Excel: Launch Microsoft Excel on your computer.
2. Go to Options: Click on the "File" menu in the top-left corner of the Excel window, then select "Options" from the menu. This will open the Excel Options dialog box.
3. Navigate to Advanced Options: In the Excel Options dialog box, click on the "Advanced" tab on the left-hand side.
4. Find Cut, copy, and paste options: Scroll down until you find the "Cut, copy, and paste" section.
5. Adjust settings: Within the "Cut, copy, and paste" section, you should see various options related to copying and pasting. Look for the option that says "Cut, copy, and sort inserted objects with their parent cells" or similar.
6. Disable linking: Uncheck the box next to the option that mentions linking or pasting objects with their parent cells. This option may be named differently depending on your version of Excel, but it will typically refer to pasting objects or data with links.
7. Apply changes: After unchecking the appropriate box, click "OK" to apply the changes and close the Excel Options dialog box.

By disabling this option, Excel should no longer create links to other workbooks by default when copying and pasting data. Keep in mind that this change will apply globally to all Excel workbooks on your computer, so you will not need to adjust settings for each individual workbook.

[#12]

@NikolinoDE 

Thanks NikolinoDE, yes, the idea is to have a global setting that stop linking workbooks. I can totally see how one can use that feature, but for many users, it just seems to add a level of frustration when links they don't know exist get broken and formulas suddenly stop working.
 
I would never have found that setting on my own: the labelling just does not seem to be related, so thanks for pointing to the right direction,  I'll see if that does the trick.
 
That said for those on mac (Excel 16.82 on macOS Sonoma), here where to find it. 
 
1 - In the top bar menu, on the left, roll down the Excel menu > Preferences
2 - Click the Edit icon (Top center)
3 - In Second row section (Cut and Paste Options), uncheck the first option: Cut, copy and sort inserted objects with related cells
 
 
 

[#13]

@Arkinien @NikolinoDE I've recently noticed this issue both in the desktop version and online. I have unchecked the box for "Cut, copy, and sort inserted objects with their parent cells", but there is no change for me. Copy/paste is still inserting references to the source workbook.

I've noticed that any formula that references an object (a table or sheet) in the workbook will create a reference to the original workbook when I copy and paste from one workbook to another. I've used the source workbook as a template for the target workbook, so the named tabel and sheets exist in the target.