Activate/Deactivate Macro

[stapuff]

I have a macro called "WritetoCell" that I would like to have run every 10 seconds only when sheet1 is active.

When sheet1 is not active...I do not want the macro to run at all


The Worksheet_Activate part works great....the Worksheet_Deactivate does not. I get an on time error

any thoughts on what to fix would be greatly appreciated.

Public dTime As Date
Private Sub Worksheet_Deactivate()
Application.OnTime dTime, "WritetoCell", , False
End Sub


Private Sub Worksheet_Activate()
dTime = Now + TimeValue("00:00:10")
Application.OnTime dTime, "WritetoCell"
End Sub

 

[GTO]

If it is to run every ten seconds, I imagine WriteToCell calls itself. Could you show us that sub?

Also - is WRiteToCell in a Standar Module?

 

[prajul89]

Use these codes

Sub Writetocell()
Application.OnTime Now + TimeValue("00:00:03"), "Writetocell"
If Sheets("Sheet1").Select = False Then Exit Sub
MsgBox "Your Codes "
End Sub

Private Sub Worksheet_Activate()
dTime = Now + TimeValue("00:00:03")
Application.OnTime dTime, "WritetoCell"
End Sub