On Error Go to....

ramasterre

Active Member
Joined
Oct 5, 2004
Messages
253
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?



Code:
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"
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
You need an Exit Sub before your error code.

Something like
Code:
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.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Tag your code:

On Error GoTo myErr
your code here!
GoTo myEnd

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

myEnd:
End Sub
 

ramasterre

Active Member
Joined
Oct 5, 2004
Messages
253

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
ramasterre said:
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? :unsure:

...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

Active Member
Joined
Oct 5, 2004
Messages
253

ADVERTISEMENT

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:

Code:
 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

Code:
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Jan 18, 2005
Messages
848
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,151
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top