How to stop a macro, that refreshes a cell automatically, with another macro?

user_16179308 0
Viewed 86

I typed a macro that refreshes/calculates a sheet automatically every second.

Sub Calculate_Range()
    Range("A1:A5").Calculate
    Application.OnTime DateAdd("s", 1, Now), "Calculate_Range"
End Sub

Source: https://www.exceltip.com/tips/auto-refresh-excel-every-1-second-using-vba-in-excel.html

Whenever I enable, it goes on infinite loop. I'd like to stop this with another macro. I tried several macros.

Is it possible to stop it without using brute force methods like ESC or stepping in?

vba excel
1 Answers

Try this code:

Option Explicit

Public stopIt As Boolean

Sub Calculate_Range()
    If Not stopIt Then
        Range("A1:A5").Calculate
        Application.OnTime DateAdd("s", 1, Now), "Calculate_Range"
    Else
        stopIt = False
    End If
End Sub

Sub Stop_Calculate_Range()
    stopIt = True
End Sub

enter image description here

user_150353140
Related