Macro reseting OnTime on every event

[mrMadCat]

Hello again.
I need a macro to be started after some time after event of selection change. If the event repeats before the timer has run off - the timer should be reset.
I've made this code but it doesn't reset time. Please help understand what is wrong.

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Application.OnTime Endtime, "Other_Macro", , False
    Endtime = Now + TimeValue("00:00:05")
    Application.OnTime Endtime, "Other_Macro", , True
End Sub

 

[mrMadCat]

Anyone? Please help. Can't solve this problem. The time doesn't reset and I get Other_Macro executed after every selection change.

 

[Jaafar Tribak]

You need to store the Endtime value in a static or module level variable ... Something like this :

[COLOR=#ff0000][B]Private Endtime As Double[/B][/COLOR]

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Application.OnTime Endtime, "Other_Macro", , False
    Endtime = Now + TimeValue("00:00:05")
    Application.OnTime Endtime, "Other_Macro", , True
End Sub

 

[mrMadCat]

THANK YOU VERY MUCH!
ray: ray: ray: