Stop Msg Box if process completed

[pjhtechnology]

I have a Workbook with links to a second Workbook - at the end of each week the user needs to run a Macro to break the links (I have this one)

As a reminder I'm currently showing a Msg Box anytime the file is closed reminding them to run the Macro if they have completed processing for the week (they can choose Yes to continue to close or No to stop the close and then run the macro)

I would like to stop showing the Msg Box if the file is used again after the links have been broken - the only thing I can think of is to check if there are any external links in the workbook before opening the Msg Box - unfortunately I cannot figure out how to check for those links in the BeforeClose macro

Any assistance is appreciated - Thanks

 

[AlphaFrog]

Dim HasExtLink As Range

    On Error Resume Next
    Set HasExtLink = Cells.Find(What:=".xls", After:=Range("A1"), LookIn:=xlFormulas, _
                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                     MatchCase:=False, SearchFormat:=False)
    On Error GoTo 0

    If Not HasExtLink Is Nothing Then
        MsgBox "Sheet has external links"
    Else
        MsgBox "Sheet does not have external links"
    End If

 

[pjhtechnology]

Thanks for the info - that works for the sheet that is current - Not all worksheets have links and there is no way to determine which worksheet the user is on when closing the workbook - is there a way to have the macro search all sheets in the workbook (it would also work if I could have it look at a specific worksheet?

 

[AlphaFrog]

Dim HasExtLink As Range, sht as Sheet, sSheetswithlinks as String

On Error Resume Next
For Each sht in Worksheets

    Set HasExtLink = sht.Cells.Find(What:=".xls", After:=sht.Range("A1"), LookIn:=xlFormulas, _
                     LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                     MatchCase:=False, SearchFormat:=False)

    If Not HasExtLink Is Nothing Then
        sSheetswithlinks  = sSheetswithlinks  & Sht.Name & " ,"
        Set HasExtLink = Nothing
    End If
    
Next sht
On Error GoTo 0

    If Len(sSheetswithlinks)>0 Then
        MsgBox sSheetswithlinks  & " sheet(s) has external links"
    Else
        MsgBox "Sheet does not have external links"
    End If