Application.ontime VBA help

[vasim]

Hello,


Q: I want a cell value to change between 1to5 after every 5 sec.

I got this from somewhere that's working fine, but I have to run the "run1min" every time I open the workbook to run the macro and again run "stopit" to stop.


Please can someone help me, the macro should start automatically when the workbook is open and stop once it is closed.


Public ntime As Date


Sub run1min()

Call rand

Calculate

End Sub


Sub rand()

Application.ScreenUpdating = False

Range("A1").Select

ActiveCell.FormulaR1C1 = "=Randbetween(1,5)"

Application.ScreenUpdating = True

done:


ntime = Now + TimeValue("00:00:05")

Application.OnTime ntime, "rand"


End Sub


Sub stopit()

Application.OnTime ntime, "rand", , False

End Sub

 

[Hui]

add these 2 routines to the Workbook module

[pre]

Private Sub Workbook_Open()
Rand
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Stopit
End Sub

[/pre]

You can delete the Run1Min subroutine

 

[vasim]

Spot on..............Thats the way aha aha.....Thanks