Problem Breaking Links in Excel

Problem Breaking Links in Excel

In the screen shot below, there are two files. Cell B4 in the worksheet at the right is linked to cell B7 in the sheet at the left. If you have a problem breaking links in Excel, this article might help fix that.

Files with Linked Cell

Here is the workbook with a cell that is linked to a different workbook.


In the file with the link, I can go to the Ribbon’s Data tab, and click the Edit Links command,


Edit Links Dialog Box

That opens the Edit Links dialog box, and shows a list of the links in the workbook.

To break the link to the other file, click the Break Links button.

When the confirmation message appears, read the dire warnings, and make a backup, if you haven’t already done that.

Then come back to the Edit Links command, and click OK.


Break Link Button Dimmed Out

I use this technique in Excel programming sometimes, when creating copies of workbooks. By breaking the links, the formula results change to values, and the file can be sent to someone who doesn’t have the original source file.

Recently, I ran into a problem – the Break Link command wasn’t working in the macro, even though it had worked nicely for several months.

So, to troubleshoot the problem, I tried to break the link manually. When the Edit Links window opened, the Break Link button was dimmed out.

The link was still showing in the list, but I couldn’t change it or break it.


Found the Problem

After several head scratching moments (or hours, I can’t remember), I finally noticed that someone (probably me) had protected the sheet that was currently active.

The sheet with the link wasn’t protected, but that didn’t matter. If the active sheet is protected, you can’t break a link in the file.

Unprotect the Sheet

So, if you’re having a problem breaking a link, check the active sheet for protection. You can do this manually, or in your VBA code.

For example, if you’re doing this in a macro, activate a specific sheet, unprotect it, then break the link, and protect the sheet again. The code below breaks the first link in the file.

Dim astrLinks As Variant
astrLinks = wbNew.LinkSources(Type:=xlLinkTypeExcelLinks)
' Break the first link in the active workbook.
On Error Resume Next
With wbNew.Sheets(1)
   .BreakLink Name:=astrLinks(1), _
On Error GoTo 0
End With