How to stop users adding links to other workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-1538220%22%20slang%3D%22en-US%22%3EHow%20to%20stop%20users%20adding%20links%20to%20other%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538220%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EWe%20use%20Excel%20for%20preparing%20budget%20files.%20These%20files%20then%20get%26nbsp%3Bcollated%20in%20MS%20Power%20BI%20for%20group%20wide%20analysis.%3C%2FP%3E%3CP%3EOne%20issue%20we%20have%20is%20that%20users%20can%20sometimes%20paste%20links%20from%20spreadsheets%20on%20their%20desktops%20into%20the%20budget%20files.%20This%20naturally%20causes%20problems%20when%20other%20users%20open%20them.%3C%2FP%3E%3CP%3EIs%20there%20any%20way%20to%20prevent%20external%20links%20from%20being%20inserted%3F%20I've%20searched%20in%20Data%20Validation%20and%20Protect%20Sheet%20but%20haven't%20found%20anything%20suitable.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1538220%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EUser%20Adoption%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1538353%22%20slang%3D%22en-US%22%3EBetreff%3A%20How%20to%20stop%20users%20adding%20links%20to%20other%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1538353%22%20slang%3D%22en-US%22%3EStop%20Automatic%20Hyperlinks%20in%20Excel%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20to%20stop%20automatic%20hyperlinks%20in%20Excel%3F%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20turn%20the%20hyperlink%20option%20off%20in%20Excel%3CBR%20%2F%3E%3CBR%20%2F%3EOn%20the%20Tools%20menu%2C%20choose%20AutoCorrect%20Options%3CBR%20%2F%3ESelect%20the%20AutoFormat%20as%20you%20type%20tab%3CBR%20%2F%3ERemove%20the%20check%20mark%20from%20Internet%20and%20network%20paths%20with%20hyperlinks%3CBR%20%2F%3EClick%20OK%3CBR%20%2F%3E%3CBR%20%2F%3ERemove%20a%20Hyperlink%20Manually%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20you%20want%20to%20leave%20the%20hyperlink%20feature%20on%2C%20you%20can%20undo%20the%20hyperlink%20immediately%20after%20it's%20created.%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20manually%20remove%20the%20hyperlink%3A%3CBR%20%2F%3E%3CBR%20%2F%3EType%20the%20email%20address%20and%20press%20Enter%3CBR%20%2F%3EImmediately%2C%20press%20Ctrl%2BZ.%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20is%20a%20shortcut%20for%20Undo%2C%20and%20will%20convert%20the%20hyperlink%20back%20to%20text.%3CBR%20%2F%3ERemove%20Selected%20Hyperlinks%20Programmatically%3CBR%20%2F%3E%3CBR%20%2F%3ETo%20change%20a%20group%20of%20cells%20that%20contain%20hyperlinks%2C%20you%20can%20use%20the%20following%20code.%20It%20deletes%20all%20the%20hyperlinks%20in%20the%20selected%20cells.%3CBR%20%2F%3E%3CBR%20%2F%3ESub%20delHyperlinks()%3CBR%20%2F%3EDim%20myCell%20As%20Range%3CBR%20%2F%3EFor%20Each%20myCell%20In%20Selection%3CBR%20%2F%3EmyCell.Hyperlinks.Delete%3CBR%20%2F%3ENext%20myCell%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3E%3CBR%20%2F%3EI%20would%20be%20happy%20to%20find%20out%20if%20I%20could%20help.%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%3CBR%20%2F%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E
New Contributor

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.

5 Replies
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)

@Nikolino , 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

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...

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 

@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)