Thread: Do not prompt user on broken link

Hi Sounds simple but have you tried creating 3 dummy files with zero values?

Wow... The thought never even occurred to me.

That's what I'll do.

But it'd still be a nice bit-o-knowledge to have for any future use. If anyone does know of a way to suppress the prompt, I'd love to know how. In the mean time, I'll use MartinjLane's method.

The solutions isn't to "pre-click" the prompt, but instead to "preempt" the the prompt. i.e. the macro checks if a file exists before it writes links to it.

In other situations the option "update remote refences" in Tools-> options -> calculation would be appropriate but you don't want to turn this off if other formulae rely on remote references (obviously they do).

CC

This is my macro:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing Then

Dim VolumeDate1 As Variant
Dim VolumeDate2 As Variant
Dim VolumeDate3 As Variant
Dim VolumeDate4 As Variant
Dim VolumeDate5 As Variant
Dim VolumeDate6 As Variant
Dim VolumeDate7 As Variant
Dim VolumeDate8 As Variant
Dim VolumeDate9 As Variant
Dim VolumeDate10 As Variant
Dim VolumeDate11 As Variant
Dim VolumeDate12 As Variant
Dim VolumeDate13 As Variant

    VolumeDate1 = Format(DateAdd("m", -1, Range("A1")), "mm-yyyy")
    VolumeDate2 = Format(DateAdd("m", -2, Range("A1")), "mm-yyyy")
    VolumeDate3 = Format(DateAdd("m", -3, Range("A1")), "mm-yyyy")
    VolumeDate4 = Format(DateAdd("m", -4, Range("A1")), "mm-yyyy")
    VolumeDate5 = Format(DateAdd("m", -5, Range("A1")), "mm-yyyy")
    VolumeDate6 = Format(DateAdd("m", -6, Range("A1")), "mm-yyyy")
    VolumeDate7 = Format(DateAdd("m", -7, Range("A1")), "mm-yyyy")
    VolumeDate8 = Format(DateAdd("m", -8, Range("A1")), "mm-yyyy")
    VolumeDate9 = Format(DateAdd("m", -9, Range("A1")), "mm-yyyy")
    VolumeDate10 = Format(DateAdd("m", -10, Range("A1")), "mm-yyyy")
    VolumeDate11 = Format(DateAdd("m", -11, Range("A1")), "mm-yyyy")
    VolumeDate12 = Format(DateAdd("m", -12, Range("A1")), "mm-yyyy")
    VolumeDate13 = Format(DateAdd("m", -13, Range("A1")), "mm-yyyy")

        
    Range("N3:N8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate1, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("M3:M8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate2, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("L3:L8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate3, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("K3:K8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate4, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
               
    Range("J3:J8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate5, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("I3:I8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate6, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("H3:H8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate7, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("G3:G8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate8, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("F3:F8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate9, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("E3:E8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate10, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("D3:D8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate11, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("C3:C8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate12, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("B3:B8").Select
    Selection.Replace What:="[Volumes ???????", Replacement:="[Volumes " & VolumeDate13, LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Range("A1").Select
        
End If

End Sub

It takes the existing/pre-filled link, and replaces the previous date in the filename with the new date.

How could I test to see if the new file exists before the replace?

Solution:

Code:

Application.DisplayAlerts = False

Then just turn the alerts back to "true" when done with code.

I can't believe how simple this was, or how long it took me to find it.

lol - at least you'll never forget this one

Sorry, it looks like this thread slipped through the "subscribed threads net" - I didn't mean to abandon you!

CC