VBA Code to prevent inclusion of external links in the Workbook

[gubertu]

Hi all,

I hope you can help me with this.

I need a code to prevent that any external link or reference can be included in my Workbook.

For example, I have my Workbook "Master" and someone wants to include in it a reference from another Workbook called "Country", let´s say in tab "Data", cell "A1".

If there is no protection, it will appear the following reference, in my Workbook "Master" = [Country]Data!$A$1.

Well, what I need is, when someone tries to do include some external reference, some message appears like "You cannot include external references in this Workbook" and do not allow to include anything.

Hope you can help me.

Thanks in advance!

 

[Yongle]

Try this

In ThisWorbook module (will not work in standard module)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Call BreakExternalLinks
End Sub

Private Sub BreakExternalLinks()
[I][COLOR=#006400]'(Code is modified slightly)Original source: www.TheSpreadsheetGuru.com/The-Code-Vault[/COLOR][/I]
    Dim ExternalLinks As Variant, wb As Workbook, x As Long
    On Error Resume Next
    Set wb = ActiveWorkbook
    ExternalLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
    On Error GoTo Handling
    For x = 1 To UBound(ExternalLinks)
        MsgBox "You cannot include external references in this Workbook"
        wb.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
     Next x
Handling:
End Sub

 

[gubertu]

This is just what I wanted.

Thanks a lot!

 

[Yongle]

thanks for feedback