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!

 

[DanteAmor]

Try this:

Sub Macro()
  Dim sPath As String, sFile As String
  sPath = "C:\trabajo\books\"
  sFile = "bookname.xlsx"
  If Dir(sPath & sFile) = "" Then
    MsgBox "File does not exists"
    Exit Sub
  End If
  '
  'here your code
  '
End Sub

 

[Indominus]

Try this:

Sub Macro()
  Dim sPath As String, sFile As String
  sPath = "C:\trabajo\books\"
  sFile = "bookname.xlsx"
  If Dir(sPath & sFile) = "" Then
    MsgBox "File does not exists"
    Exit Sub
  End If
  '
  'here your code
  '
End Sub

Thanks for the response. So I’m little confused with your code. The file can be saved in any path and not sure what to put between “”

 

[DanteAmor]

So I am not understanding what you need.
Better put your code here.
Tell me what you want to put in the code and where you want it.

 

[Indominus]

So I am not understanding what you need.
Better put your code here.
Tell me what you want to put in the code and where you want it.

When that error comes up (1004) stating the file may have been moved or renamed. Display a message box. The file name should always be titled MC Macro Master. There are multiple references to this workbook name in the long macro I have.

 

[DanteAmor]

I do not understand what do you need.
You didn't put your code either.
This is the syntax to check if a file exists in a path:

 If Dir(Path & File) = "" Then
    MsgBox "The file does not exists"
  Else
     MsgBox "Yes exists"
  End If

 

[Indominus]

I do not understand what do you need.
You didn't put your code either.
This is the syntax to check if a file exists in a path:

If Dir(Path & File) = "" Then
    MsgBox "The file does not exists"
  Else
     MsgBox "Yes exists"
  End If

[/
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
    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
   
End Sub



]

 

[Michael M]

 The file can be saved in any path

So you want the macro to search the entire hard drive for the file ??
That would be a big search !!

 

[Indominus]

 The file can be saved in any path

So you want the macro to search the entire hard drive for the file ??
That would be a big search !!

No, the path doesn’t matter. If the file name in all the lines of code that reference the name does not match then show a message box. Not the error

 

[Michael M]

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.