excel - Prevent the "Update Values:" dialog box from opening every time a cell with a link is modified

Question

Quick version: I've got broken links in a file I'm working with because they're pointing to someone else's hard drive. A macro went wrong in someone else's file that converted all formulas to text by appending an apostrophe before the formula. I wrote a macro to fix this, but there are a ton of external links in the file. The macro essentially changes a formula from the first line to the second line below, doing nothing more than removing the unnecessary apostrophe.

1) '='C:OtherPersonsFolderPath[FileName.xlsm]Sheet1'!A1
2)  ='C:OtherPersonsFolderPath[FileName.xlsm]Sheet1'!A1

If I do this manually, Excel opens a dialog box asking me to "Update Values" in FileName.xlsm by pointing to the right file. I don't want to update the file path though: I plan to give this back to the original owner of the file with all paths in tact, sans apostrophes. If I hit the "cancel" button on that dialog box, I get the intended effect: The formula updates to what I need, and the value changes to whatever it used to be back when it was a working link. It works fine if I manually hit "cancel" on the box every time it pops up, but I've got thousands of cells to iterate through across dozens of sheets. I need a way to tell VBA to say "cancel" in that box, or prevent the box from appearing in the first place. Any ideas? My code is below:

Public Sub MyBugFix()

Application.Calculation = xlCalculationManual
'Note that I unsuccessfully tried options like "ThisWorkbook.UpdateLinks = xlUpdateLinksNever" and "Application.DisplayAlerts = False" here

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count
    Sheets(I).Visible = True
    Sheets(I).Select
    Range("A1:BZ400").Select

    'Simple fix for embedded apostrophes in formulas (e.g., an equals sign in an IF statement)
        Selection.Replace What:="'=", Replacement:="=", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    'More complex fix for apostrophes at the start (they're special characters, so the find/replace trick doesn't work)
        Dim myRng As Range
        Dim myCell As Range
        Set myRng = Nothing
        On Error Resume Next
        Set myRng = Intersect(Selection, _
        Selection.Cells.SpecialCells(xlCellTypeConstants))
        On Error Resume Next

        For Each myCell In myRng.Cells
            If myCell.PrefixCharacter <> "" Then
            myCell.Value = "" & myCell.Text
            On Error Resume Next
            End If
        Next myCell
Next I

Application.Calculation = xlCalculationAutomatic

End Sub

Answer 1

I found a solution here: http://www.mrexcel.com/forum/excel-questions/506273-turn-off-update-values-dialog-box.html so I can't claim any credit for it!

Put this before you edit the formula ...

ThisWorkbook.UpdateLinks = xlUpdateLinksNever

Then turn it back on after you have made your edits...

ThisWorkbook.UpdateLinks = xlUpdateLinksAlways

This solved a similar problem for me, where I was using VBA to write cell formula containing references to other spreadsheets. All credit goes to AlphaFrog on the MrExcel forum!