Stop Msg Box if process completed

pjhtechnology

New Member
Joined
Mar 13, 2010
Messages
7
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,358
Code:
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

New Member
Joined
Mar 13, 2010
Messages
7
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,358
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,567
Messages
5,572,958
Members
412,491
Latest member
tweetytoon
Top