How to fix – This workbook contains one or more links that cannot be updated…

Posted on January 28, 2021 by

Ok, if you are visiting this article, then probably the reason is that you have received the following messagebox upon openning of Excel:

And after looking all over your cells, with both VBA and “Find”, you have not found anything even slightly reminding a link. Then you have started wondering more and more, thinking where you did wrong in your entire life to be forced to earn a living as an Excel developer. Somewhere at this point you have found out this resource. And you may smile, because hopefully it will be useful – the hidden link is actually in the conditional formatting formula. How did it appear there? Noone knows, but it is ok, because you are going to delete it once and for all. With big satisfaction and a huge smile on your face. Try not to break your keyboard, while doing it! 🙂

Simply run this VBA code and it will be displayed in the immediate window (Ctrl+G):

Sub ErrorInFormulas()

    Dim ws As Worksheet, r As Range
    Dim cf As FormatCondition

    For Each ws In Worksheets    
        For Each r In ws.UsedRange
            If IsError(r) Then
                Debug.Print r.Parent.Name, r.Address, r.Formula
            End If
        Next
        
        For Each cf In ws.Cells.FormatConditions
            Debug.Print cf.AppliesTo.Address, cf.Type, cf.Formula1, cf.Interior.COLOR, cf.Font.Name
        Next
    Next
    
End Sub

Sub ErrorInFormulas()

Dim ws As Worksheet, r As Range

Dim cf As FormatCondition

For Each ws In Worksheets

For Each r In ws.UsedRange

If IsError(r) Then

Debug.Print r.Parent.Name, r.Address, r.Formula

End If

For Each cf In ws.Cells.FormatConditions

Debug.Print cf.AppliesTo.Address, cf.Type, cf.Formula1, cf.Interior.COLOR, cf.Font.Name

End Sub

Enjoy!

Tagged with: error, links, that cannot be updated, VBA, workbook

How to fix – This workbook contains one or more links that cannot be updated…

Related posts: