If the status of the scheduler is changed to False, find out in [schedule_next] cell Else the next execution On Error GoTo Error_Handler_1 time of the Application.OnTime range("schedule_next").Value, scheduled "Periodic", False “Periodic” On Error GoTo 0 subroutine and End If remove it. If errors occur while removing, go to the error handler. Protecting the worksheet and Worksheets(1).Protect Password:="" quitting the Exit Sub subroutine . We land here if the scheduled procedure is not “Periodic” found. Such a situation will occur if you exit the Excel file without stopping Error_Handler_1: the scheduler and MsgBox "nothing to stop" then try to stop the On Error GoTo 0 procedure after Worksheets(1).Protect Password:="" reopening the Excel file. A message will be displayed that there is nothing to stop, the worksheet will be protected, and the subroutine will quit. End of the schedule End Sub subroutine Beginning of the subroutine periodic. The subroutine does Sub Periodic() the actual stuff and Worksheets(1).Unprotect also, upon the Dim schedule_next As completion, Dim schedlue_report As range schedules itself for Set schedule_next = range("schedule_next") Set schedule_report = range("schedule_report") the next execution. We begin by creating two
Else On Error GoTo Error_Handler_1 Application.OnTime range("schedule_next").Value, "Periodic", False On Error GoTo 0 End If If the status of the scheduler is changed to False, find out in [schedule_next] cell the next execution time of the scheduled “Periodic” subroutine and remove it. If errors occur while removing, go to the error handler. Worksheets(1).Protect Password:="" Exit Sub Protecting the worksheet and quitting the subroutine . Error_Handler_1: MsgBox "nothing to stop" On Error GoTo 0 Worksheets(1).Protect Password:="" We land here if the scheduled procedure “Periodic” is not found. Such a situation will occur if you exit the Excel file without stopping the scheduler and then try to stop the procedure after reopening the Excel file. A message will be displayed that there is nothing to stop, the worksheet will be protected, and the subroutine will quit. End Sub End of the schedule subroutine Sub Periodic() Worksheets(1).Unprotect Dim schedule_next As Dim schedlue_report As range Set schedule_next = range("schedule_next") Set schedule_report = range("schedule_report") Beginning of the subroutine periodic. The subroutine does the actual stuff and also, upon the completion, schedules itself for the next execution. We begin by creating two
index7.docx