On Error Go to....

[ramasterre]

I have a macro that opens up various documents based on input in a userform. the macro looks up the document name and if it cannot find it then an error message pops up telling the user to try again. BUT even when the document is found the error message still pops up. I figured out that it is because there is that pesky "this workbook has information linked to another...blah blah blah...would you like to update?" message. Well, no, i would not like to update. I tried disabling the message but, it still triggers my error message to run. Is there a way to disable the update message or to modify my code to ignore it?

Workbooks.Open FileName:="S:\EVERYONE\Legal Entity\KSmith\Fed Reporting\FR2900" & "\" & x & "\" & MMM & "\" & "FR2900" & " " & mon & "-" & da
  'Here is where the file will open and show the update message
Application.DisplayAlerts = False
uView2900.Hide
Unload uView2900 'this is just the message box
Application.DisplayAlerts = True

If Error Then GoTo msgbox2
msgbox2:
MsgBox "The Fr2900 for the date entered cannot be found. Dates must be in mm/dd/yyyy format", vbAbortRetry = vbAbortRetryIgnore, "Date Not Found"

 

[ramasterre]

Ok, well the error that I am looking to tag is if the user enters a date and a file cannot be found with that date. Is there a way to say, IF the file is not sucessfully opened THEN run message box?

 

[Joe Was]

This Custom Function and Sub work together to check if a file is found.
You will need to change the Sub to not use the InPutBox to get the file name, just load the "FolderFile" Var. with your cell value and blend the resulting code into whatever Sub you are working with or use it as a stand alone. To use it as an automatic stand alone you will need to add the loop code!

As it is now it asks you for the "Drive:\Folder\FileName.ext" and tests for that file. Also, comment out the "Does Exist" MsgBox if you loop the code!

Private Function FileExists(fName) As Boolean
'Custom Sheet Function!
'Syntax: =FileExists("Drive:/Folder(s)/FileName")
'If file is found: Returns TRUE, else FALSE.
Dim myFolder As String

myFolder = Dir(fName)

If UCase(fName) = "HELP" Then
MsgBox " Syntax: =FileExists(""Drive:/Folder(s)/FileName"")" & vbCr & vbCr & _
"Like ==> =FileExists(""C:\Path\FileName.xls"")"
End If

If myFolder <> "" Then
FileExists = True
Else
FileExists = False
End If
End Function

Sub CKForFile()
'Check for a file!
Dim folderFile As String
Dim Message, Title, Default

Message = "Enter a Drive:/Folder/FileName" ' Set prompt.
Title = "Check for File" ' Set title.
Default = "C:\Test.txt" ' Set default.
' Display message, title, and default value.
folderFile = InputBox(Message, Title, Default)
'folderFile = "C:\Path\FileName.xls"

If FileExists(folderFile) Then
MsgBox folderFile & " Does Exist!"
Else
MsgBox folderFile & " Not Found?"
End If
End Sub

 

[ramasterre]

Thank you...you're a peach! I appreciate the help ray: