automatically place number in cell 5 sec intervals

[slochin]

Hello,
Hoping someone might be able to assist please.
I am running a program which updates some results if you place -6 in the q2 cell.I have been doing this manually but I wondered if there was a formula I could put in that would place the minus 6 (-6) number in the q2 cell say every 5 seconds.
Appreciate any help.Thank you

 

[Logit]

.
Paste the following in a module :

Option Explicit


Dim TimeToRun


Sub auto_open()
    Call ScheduleCopyPriceOver
End Sub


Sub ScheduleCopyPriceOver()
    TimeToRun = Now + TimeValue("00:00:05")
    Application.OnTime TimeToRun, "sTARTM"
End Sub


Sub Startm()
    Calculate
    Range("Q2").Value = "-6"
    Call ScheduleCopyPriceOver
End Sub


Sub Stopm()
    On Error Resume Next
    Application.OnTime TimeToRun, "Startm", , False
End Sub

Place two command buttons on the sheet. One attached to the Startm macro and the other attached to the Stopm macro.

 

[slochin]

Hello Logit ,
Thank you for your prompt response.Do I take it that this requires the start button to be selected every time I start the Excel sheet? If so is it possible to have this made to start when I open the Excel sheet as all the other parts are automatic.Appreciate your assistance.

 

[Logit]

.
You can have it run without the buttons. Just paste the macros in a module and save as .XLSM (macro enabled).

When you open the workbook, the macro auto runs itself. However, without a command button you won't be able to stop the macro
unless you close the workbook. Next time you open it, the macro auto runs again.

 

[slochin]

Hello Logit,
Many thanks for taking the time to assist me and to provide an excellent solution to my query.

 

[Logit]

You are welcome. Glad to help.