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

3 Replies
Highlighted
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)
Highlighted

@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

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