Macro running in the background, doing a task once a minute

Hi Atanas,

If you click on the link below you will find a very good explanation about ho to create open workbook events.

https://www.excel-easy.com/vba/events.html

You will not need to put any code in a open workbook event to do what you want, but by calling the timed macro from the event the repeating task will start automatically when you open the file.

You can also start the process by executing the MyTimedCode.

Sorry I forgot an important line in my answer earlier. I didn't call MyTimedCode in the DoSomething macro.


Sub MyTimedCode()
Application.Ontime Now+Timevalue("00:01:00"), "DoSomething"
End Sub

Sub DoSomething()

"Type the code here you want to execute every minut"
Call MyTimedCode
End Sub


If you want it to stop if you have 0 in lets say A1:

Sub DoSomething()

"Type the code here you want to execute every minut"

If Range("A1")=0 then
Exit Sub
else
Call MyTimedCode
End If
End Sub

It is more complicated if you want pause. You will have to use a Worksheet Change event, which will execute every time the cell with 0 or 1 change value. We did a worksheet change event on the course. We told Excel to calculate when something was changed in a range.

Please have a look at the linked below. This explain how to create Worksheet change events.

https://docs.microsoft.com/en-us/office/troubleshoot/excel/run-macro-cells-change

To Kill the code. If you in the change event add a If testing let say

if Range("b2")="Kill" then
Exit sub
End If

Then by typing kill in B2 you stop the code.

I hope this makes sense.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector