How to have a message box appear if referenced workbook name is not correct?

[Indominus]

Hello. I made a long macro for myself and coworkers that references one static workbook multiple times. However, I have to edit the coding behind it once in a while and resend it. When this happens they are told to delete the others and make sure the name is the same. However, some are not tech savvy so sometimes they get the error code 1004, that says Sorry, we couldn't find "Workbook Name", Is it possible it was moved , renamed or deleted?

How could I add a code right in the beginning of my macro that will make a message box appear with custom text I want if the file is not correct (when it detects the error? The text I am going to put is what to do if they see the error. Thank you!

 

[Indominus]

It may be possible to manipulate the displayed error message but slight problem is the 1004 error covers a multitude of sins but there maybe a workaround that might do what I think you want

Sub Test()
On Error GoTo ErrHandler:
    Application.Run "'MC Macro Master.xlsm'!ChangeCxTimes"

  'rest of your code




'place this at bottom of your code
ErrHandler:
If Err <> 0 Then
    If Err.Description Like "Sorry, we couldn't find*" Then
    Err.Description = Replace(Err.Description, Err.Description, "Your Custom Text Here")
    End If
    MsgBox (Error(Err)), 48, "Error"
End If
End Sub

Change BOLD text for message you want.

Idea not fully tested but if does what you want, that particular error should see its message changed to your custom message - all other errors should display as normal.

Hope Helpful

Dave

This works perfectly! Thank you so much!! I really appreciate. Thanks to all others who helped also.

 

[dmt32]

you are welcome - Glad we were able to help
Many thanks for feedback, it is appreciated by all.

Dave