Hi, I am using below code to do a task on a particular time as in a cell..

Application.OnTime TimeValue(CDate([Totals!O2])), "Assign"

But this takes the time whatever is there in the cell while workbook is
being opened, but I need to trigger a macro on time even after editing the
time in the cell.

Please help..

Thanks.

Use a worksheet change to cancel old OnTime event and then set a new time.

Sub worksheet_change(ByVal target As Range)

For Each cell In target
If Not Application.Intersect(cell, Range("O2")) Is Nothing Then

'cancel old on time event
Application.OnTime _
EarliestTime:=0, _
procedu="Assign", _
schedule:=False

Application.OnTime _
EarliestTime:=TimeValue(target.Value), _
procedu="Assign", _
schedule:=True
End If
Next cell

End Sub


"Kash" wrote:

Hi, I am using below code to do a task on a particular time as in a cell..

Application.OnTime TimeValue(CDate([Totals!O2])), "Assign"

But this takes the time whatever is there in the cell while workbook is
being opened, but I need to trigger a macro on time even after editing the
time in the cell.

Please help..

Thanks.

I am getting below error

Method 'OnTime' of object'_Application' failed

I'm not usre what line you are getting this problem. I suspect that you are
trying to clear the event before you have set the event. You may need to use
another cell to check if the timer has been set or not set. Maybe an OnError
statement

Sub worksheet_change(ByVal target As Range)

For Each cell In target
If Not Application.Intersect(cell, Range("O2")) Is Nothing Then

On Error Resume Next
'cancell old on time event
Application.OnTime _
EarliestTime:=0, _
procedu="Assign", _
schedule:=False
On Error GoTo 0
If Err.Number < 0 Then
Application.OnTime _
EarliestTime:=TimeValue(target.Value), _
procedu="Assign", _
schedule:=True
End If
End If
Next cell

End Sub





"Kash" wrote:

I am getting below error

Method 'OnTime' of object'_Application' failed

I'm not able to get any output from the above code.. :(

I added message boxes to the code below to help you debug the problem.

Sub worksheet_change(ByVal target As Range)

For Each cell In target
If Not Application.Intersect(cell, Range("O2")) Is Nothing Then

On Error Resume Next
msgbox("Cancel old on timer event")
Application.OnTime _
EarliestTime:=0, _
procedu="Assign", _
schedule:=False
On Error GoTo 0
If Err.Number < 0 Then
msgbox("Setting New Timer")
Application.OnTime _
EarliestTime:=TimeValue(target.Value), _
procedu="Assign", _
schedule:=True
else
msgbox("Error while clearing old event")
End If
End If
Next cell

End Sub



"Kash" wrote:

I'm not able to get any output from the above code.. :(

I'm able to get both the msg

msgbox("Cancel old on timer event") & msgbox("Setting New Timer") but still
macro is not getting triggered.

Hi Kash,

Did you activate the ontime event? Best is to put the application.ontime
statement in an event procedure of the workbook or worksheet.

Sub ontime()
Application.ontime EarliestTime:=Range("A10"), Procedu="Test"
End Sub

Sub test()
MsgBox Prompt:="hello"
End Sub

Wkr,

JP

"Kash" wrote in message
...
I'm able to get both the msg

msgbox("Cancel old on timer event") & msgbox("Setting New Timer") but
still
macro is not getting triggered.