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]

Well I tend to disagree....Are you saying the file will / should exist in the current directory ?
If not, then the Code WILL need a Path to search. for the file.
If the file has been moved to ANY location, then the code will need to search the entire HDD.

There’s no way to just replace the error pop up window with a pop up message box instead? I thought there was a way. If not then that’s where my confusion comes from. Never thought a file path would be needed. My coworkers can save it anywhere. Don’t want to confuse them even more.

 

[Michael M]

You can bypass the Runtime error message by Using "On Error Resume Next" ...BUT...you then can't insert a further Message box, because you have bypassed the error in the first place. Does that make sense !!

 

[Michael M]

Try it this way...if I understand you correctly......UNTESTED

Sub Test()

    Sheets("Cx Times").Select
    Range("A1:A4").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Columns("A:A").EntireColumn.AutoFit
    On Error GoTo ErrHandler:
    Application.Run "'MC Macro Master.xlsm'!ChangeCxTimes"
    Sheets("DP").Select
    Application.Run "'MC Macro Master.xlsm'!Expand"
   
    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Exit For
    End If
    Next w
   
    Application.Run "'MC Macro Master.xlsm'!MatchTimes"
    Application.Run "'MC Macro Master.xlsm'!sortpo1"
    Application.Run "'MC Macro Master.xlsm'!po1"
    Application.Run "'MC Macro Master.xlsm'!Match"
   
    Windows("MC Macro Master.xlsm").Activate
    Sheets("C1").Select
   
    Application.Run "'MC Macro Master.xlsm'!EditWP1"
    Application.Run "'MC Macro Master.xlsm'!CP1"
    Application.Run "MC Macro Master.xlsm'!AdjustWP1"
    Application.Run "'MC Macro Master.xlsm'!HighlightCellsWithData"
   
   
    Range("R16").Select
ErrHandler:  MsgBox "The file has been moved" 'Change messgae to suit
End Sub

 

[DanteAmor]

There’s no way to just replace the error pop up window with a pop up message box instead? I thought there was a way. If not then that’s where my confusion comes from. Never thought a file path would be needed. My coworkers can save it anywhere. Don’t want to confuse them even more.

It is not instead of, in vba there are tools to avoid errors, the best practice is to check if the file exists before falling into the error.

 

[Indominus]

Try it this way...if I understand you correctly......UNTESTED

Sub Test()

    Sheets("Cx Times").Select
    Range("A1:A4").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    Columns("A:A").EntireColumn.AutoFit
    On Error GoTo ErrHandler:
    Application.Run "'MC Macro Master.xlsm'!ChangeCxTimes"
    Sheets("DP").Select
    Application.Run "'MC Macro Master.xlsm'!Expand"
  
    For Each w In Workbooks
    If UCase(w.Name) Like UCase("*Pick*order*") Then
    Windows(w.Name).Activate
    Exit For
    End If
    Next w
  
    Application.Run "'MC Macro Master.xlsm'!MatchTimes"
    Application.Run "'MC Macro Master.xlsm'!sortpo1"
    Application.Run "'MC Macro Master.xlsm'!po1"
    Application.Run "'MC Macro Master.xlsm'!Match"
  
    Windows("MC Macro Master.xlsm").Activate
    Sheets("C1").Select
  
    Application.Run "'MC Macro Master.xlsm'!EditWP1"
    Application.Run "'MC Macro Master.xlsm'!CP1"
    Application.Run "MC Macro Master.xlsm'!AdjustWP1"
    Application.Run "'MC Macro Master.xlsm'!HighlightCellsWithData"
  
  
    Range("R16").Select
ErrHandler:  MsgBox "The file has been moved" 'Change messgae to suit
End Sub

I get same error.

 

[Indominus]

It is not instead of, in vba there are tools to avoid errors, the best practice is to check if the file exists before falling into the error.

Well technically the file can exist in their hard drive. They just had a duplicate open MC Macro Master(1).xlsm for example. So this wouldn’t work either.

 

[Michael M]

Ok then, move this line

On Error GoTo ErrHandler:

to just after the Start of the sub

Sub Test

 

[Indominus]

Ok then, move this line

On Error GoTo ErrHandler:

to just after the Start of the sub

Sub Test

I did this. For some reason the message box pops up every time. Even if the code functions normally. If it functions normally it pops up while executing the full code

 

[Michael M]

Put "Exit Sub" on the line before the Errhandler
Here

Range("R16").Select
Exit Sub
ErrHandler:  MsgBox "The file has been moved" 'Change message to suit
End Sub

 

[dmt32]

There’s no way to just replace the error pop up window with a pop up message box instead?

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