How to stop application.onTime loop

Question

Sub hey()
Range("C1:C59") = Range("B1:B59").Value
Application.OnTime Now + TimeValue("00:00:10"), "Hey"
End Sub

This code not stopping by stop button. how to stop this code.

Answer 1

Try pressing Ctrl+Break to pause code execution, then select Run > Reset in the Visual Basic Editor.

In the future, do it like this:

1. At the top of a standard module, declare a variable:

Public dtmNext as Date

2. Change your macro as follows:

Sub hey()
Range("C1:C59") = Range("B1:B59").Value
dtmNext = Now + TimeValue("00:00:10")
Application.OnTime dtmNext, "Hey"
End Sub

3. Copy the following code into the ThisWorkbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime dtmNext, "Hey", , False
End Sub

This stops the OnTime loop when you close the workbook.