Forum Discussion
How to stop users adding links to other workbooks
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)
- 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 ?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)
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)
