Timer does not countdown in 1-second interval.

[Excel in Curiosity]

I am trying to run a macro when 2 cells have the same value, here's my code:

'---------------------------------------------------------------------------------------
Sub timer()

interval = Now + TimeValue("00:00:01")

If Range("F16").Value = 0 Then Exit Sub

Range("F16") = Range("F16") - TimeValue("00:00:01")

Application.OnTime interval, "timer"

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("F9") = Range("F10") Then

timer

End If


End Sub
'--------------------------------------------------------------------------------------

Timer.xlsm
	A	B	C	D	E	F	G
1
2
3
4
5
6
7
8
9					Value of A:	12
10					Value of B:	12
11
12
13
14
15
16					Timer:	00:00.0	0.00000000
Sheet1

Cell Formulas
Range	Formula
G16	G16	=F16

The macro is in sheet1. When cell F9 and cell F10 is equal, the timer falls straight to 0 (from 10 seconds [00:00:10 in F16] to 0 with NO 1-second interval)

But when I only run the Sub Timer() as a module, through the macro dialog box, the countdown works just fine.

What am I missing? Please help

 

[davesexcel]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub            ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("F9:F10")) Is Nothing Then ' indicates the Target range
        If Range("F9") = Range("F10") Then
            timer
        End If
    End If
End Sub

Sub timer()

    interval = Now + TimeValue("00:00:01")
    If Range("F16").Value = 0 Then Exit Sub
    Range("F16").Value = Range("F16").Value - TimeValue("00:00:01")
    Application.OnTime interval, "timer"

End Sub

 

[Excel in Curiosity]

Thank you Dave, I am not in front of a computer right now. Will report back after trying.

 

[Excel in Curiosity]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub            ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("F9:F10")) Is Nothing Then ' indicates the Target range
        If Range("F9") = Range("F10") Then
            timer
        End If
    End If
End Sub

Sub timer()

    interval = Now + TimeValue("00:00:01")
    If Range("F16").Value = 0 Then Exit Sub
    Range("F16").Value = Range("F16").Value - TimeValue("00:00:01")
    Application.OnTime interval, "timer"

End Sub

Hello Dave, I just managed to try out your code, but now it won't countdown at all. It stays at 00:00:10.
Previously with my code, it counts down from 00:00:10 to 00:00:00, skipping everything in between (00:00:09 to 00:00:01)

 

[StephenCrump]

In Sub timer, this line:

Range("F16") = Range("F16") - TimeValue("00:00:01")

will trigger another call of Sub Worksheet_Change, which will call timer, which will write to F16 and trigger another call of Sub Worksheet_Change etc etc.

These recursive calls will bottom out when this condition is satisfied:

If Range("F16").Value = 0 Then Exit Sub

To make changes to a worksheet and not trigger Sub Worksheet_Change, you'll need to do this:

Application.EnableEvents = False
'make changes
Application.EnableEvents = True

 

[Excel in Curiosity]

Stephen, where do you suggest I put the

 Application.EnableEvents = False

I tried to put that in sub timer() and also did the following, but the timer still won't countdown:

 Sub timer()

     interval = Now + TimeValue("00:00:01")

     If Range("F16").Value = 0 Then Exit Sub
    

     Range("F16") = Range("F16") - TimeValue("00:00:01")


     Application.OnTime interval, "timer"
    
    
    

 End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub            ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("F9:F10")) Is Nothing Then ' indicates the Target range
        If Range("F9") = Range("F10") Then
            timer
            Application.EnableEvents = False
        End If
    End If
End Sub

I apologize, if I am just too dumb. But this is my very first VBA code ever.

 

[StephenCrump]

But this is my very first VBA code ever.

No problem, I could have made it clearer ....

Sub timer()

    interval = Now + TimeValue("00:00:01")
    
    If Range("F16").Value = 0 Then Exit Sub
    
    Application.EnableEvents = False
'   now this next line won't trigger Sub Worksheet_Change
    Range("F16") = Range("F16") - TimeValue("00:00:01")
    Application.EnableEvents = True
    
    Application.OnTime interval, "timer"

End Sub

 

[Excel in Curiosity]

No problem, I could have made it clearer ....

Sub timer()

    interval = Now + TimeValue("00:00:01")
   
    If Range("F16").Value = 0 Then Exit Sub
   
    Application.EnableEvents = False
'   now this next line won't trigger Sub Worksheet_Change
    Range("F16") = Range("F16") - TimeValue("00:00:01")
    Application.EnableEvents = True
   
    Application.OnTime interval, "timer"

End Sub

No problem, I could have made it clearer

I really appreciate you coming back to me. I am just not experienced at all with VBA.

About the code, it finally counts down but it would go from 10, to 9, to 7, to 3, then to 0. The countdown is erratic.
But if I run the timer module (as opposed to worksheet) using the macro dialog box, it counts down just fine from 10 to 0.

Is there anything I can do about that?

 

[Excel in Curiosity]

Actually, I just found out that running "sheet1.timer" from the macro dialog box also counts down just fine.
It is only if the timer is triggered by F9=F10, that the countdown is consistently erratic.

What can I do?

 

[Excel in Curiosity]

Ok, it seems I figured it out. The time subtraction was done twice.
Thanks so much Stephen!