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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,437
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,437
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top