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"

 

[Von Pookie]

You need an Exit Sub before your error code.

Something like

If Error Then GoTo msgbox2 
Exit sub

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

This way if it does not find an error, it will hit the Exit Sub statement and not run the msgbox 2 code. If it does hit an error, it will skip the Exit statement and run the msgbox2 code.

 

[ramasterre]

Wait....now my message box is completely disabled

 

[Joe Was]

Tag your code:

On Error GoTo myErr
your code here!
GoTo myEnd

myErr:
If Error tests 1, 2, 3...

myEnd:
End Sub

 

[ramasterre]

I dont completely understand what you mean by that. Could you explain further?

Or, is there a way to say IF the Workbook.Open code did open that file THEN turn off the error message???

 

[Von Pookie]

Wait....now my message box is completely disabled

Are you talking about the userform in your code, or the actual msgbox in the "msgbox2" code?

...although I just noticed you are using the statement of if error goto...

Try changing that to On Error goto msgbox2

With my example, your code should look something like this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> test()

<SPAN style="color:#007F00">'this tells the macro to go to the section of code</SPAN>
<SPAN style="color:#007F00">' labeled 'msgbox2' if there is an error</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> msgbox2

Workbooks.Open Filename:="S:\EVERYONE\Legal Entity\KSmith\Fed Reporting\FR2900" & "\" & x & "\" & MMM & "\" & "FR2900" & " " & mon & "-" & da
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
uView2900.Hide
Unload uView2900
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#007F00">'since this is the end of your code, we put 'Exit Sub' here so the macro</SPAN>
<SPAN style="color:#007F00">'will not continue on to show the error message when it is not required.</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#007F00">'this is where your macro will jump to when there is an error</SPAN>
<SPAN style="color:#007F00">'if there is NOT an error, this will not run and the macro will</SPAN>
<SPAN style="color:#007F00">'end with the 'Exit Sub' statement.</SPAN>
msgbox2:
MsgBox "The Fr2900 for the date entered cannot be found. Dates must be in mm/dd/yyyy format", vbAbortRetry = vbAbortRetryIgnore, "Date Not Found"

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[ramasterre]

Von Pookie

I got what you said before, I meant I didnt understand Joe Was....


Yeah, I used On Error Go To and it still didnt work, thats why I switched to If Error Go To. I have another line of code which is also having the same problem. The On Error doesnt seem to be working correctly. Here is what I have for both:

 NameFile = Yr & "\" & jjj & "\" & MMM & Dy & ".xls"
       ' Complete filename should be XYZBjjjh.PRN where jjj=Julian Date & h=hour
 ChDir SendPath
On Error GoTo Ignore
ActiveWorkbook.SaveAs FileName:=SendPath & NameFile, _
        FileFormat:=xlWorkbookNormal, CreateBackup:=False
Exit Sub
Ignore:
MsgBox "Action Cancelled, File Not Saved!"


End Sub

Workbooks.Open FileName:="S:\EVERYONE\Legal Entity\KSmith\Fed Reporting\FR2900" & "\" & x & "\" & MMM & "\" & "FR2900" & " " & mon & "-" & da

uView2900.Hide
Unload uView2900
On Error GoTo msgbox2
Exit Sub
msgbox2:
MsgBox "The Fr2900 for the date entered cannot be found. Dates must be in mm/dd/yyyy format", vbAbortRetry = vbAbortRetryIgnore, "Date Not Found"

End Sub

Am I missing something?? In Both instances the msgbox wont appear at all.

 

[Joe Was]

You are missing the fundemental property of code flags!

A code flag is an anchor for a GoTo to re-direct the flow of code to.
If you do not re-direct the code flow the code flag becomes transparent to the flow of the code, that is your Tag does not re-direct the code. So unless you re-direct the flow of code again your tag code is read as if it did not have a tag-flag at all. Your code will continue to be read and run unless you re-direct it around that error code with another GoTo. Hence my sample above.

After your code runs successfully you must add another GoTo Tag to re-direct your flow around and below the error code, by placing your End-Flag-Tag below the error code!

P.S. Try adding "End" above your "Exit Sub" statement in the code you posted this may work?

 

[chiello]

ramasterre,
the code suggested by Von Pookie is your solution.
You have simply to place the statement On Error GoTo msgbox2 before the open statement, or better, at the beginning of your code.

Try and trust...

 

[Joe Was]

Note: You only posted code snipits, so if you try to use more than on "On Error" statements only one will work per Sub!

If you test for individual errors or conditions then you can have as many GoTo Tags as needed and not use the "On Error" at all, unless it is used for a un-conditioned or unexpected error not caught by your GoTo Tag re-directs!