Err handler to stop execution

autigers

Board Regular
Joined
Oct 9, 2005
Messages
139
Hello ... getting close to testing with this thing ..
I have the code below which executes mutiple macros within ...
What I would like to have happen is that if there is an error after the Sheets("EETimecard").select occurs then I wish to have a message box and the code to stop ....

Code:
Sub RunDailyTotals()
On Error GoTo TheEnd

Application.ScreenUpdating = False
Application.Run ("InputChange")
Application.Run ("EETime")
MsgBox ("You are about about to update your shift end numbers.  ")



Sheets("EETimecard").Select
'Sheets("EETimecard").PrintOut Copies:=2, Collate:=True
MsgBox ("Please check the printer. The Employee Timcard is complete for  ") & Date

Application.Run ("Cal_DailyTotals")
Application.Run ("ExportHours")

MsgBox ("Daily Totals Completed. You must now fill in the Staffing & Daily Numbers ")
Application.Run ("Open_DailyProd")

TheEnd: Sheets("Log").Visible = xlSheetVisible

'add username date and time
    Sheets("Log").Range("B65536").End(xlUp).Offset(1, 0).Value = UserName
    Sheets("Log").Range("C65536").End(xlUp).Offset(1, 0).Value = NameOfComputer
    Sheets("Log").Range("D65536").End(xlUp).Offset(1, 0).Value = Date
    Sheets("Log").Range("D65536").End(xlUp).NumberFormat = "dd mmm yyyy"
    Sheets("Log").Range("E65536").End(xlUp).Offset(1, 0).Value = Time
    Sheets("Log").Range("G65536").End(xlUp).Offset(1, 0).Value = "User Err on End of shift"
    Sheets("Log").Range("H65536").End(xlUp).Offset(1, 0).Value = Err.Description
    Application.ScreenUpdating = True
    Sheets("DailyInput").Select
End Sub
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

micarlson

New Member
Joined
Jul 30, 2003
Messages
17
I'm not sure about jumping between procedures, but a simple albeit not so clean answer would be to put the "On Error Goto " statment in each procedure that way when an error occurs each procedure would be set up to handle it in the fashion you desire. You would need a global variable to make sure that when you returned to the calling procedure, the code would stop.

In your controlling procedure declare the Public variable. In my example Flag is the global/public variable.


On Error goto GetOut


GetOut:
on error goto 0
Flag =1
msgbox.....
exit sub

after each call, insert an if statement to exit the sub if Flag=1

Application.Run ("Cal_DailyTotals")
if Flag = 1 then exit sub
Application.Run ("ExportHours")
If Flag = 1 then exit sub


Hope that's not too confusing. Helpful or no?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,812
Messages
5,574,463
Members
412,595
Latest member
slim313
Top