Timer does not countdown in 1-second interval.

Excel in Curiosity

New Member
Joined
Sep 15, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFG
1
2
3
4
5
6
7
8
9Value of A:12
10Value of B:12
11
12
13
14
15
16Timer:00:00.00.00000000
Sheet1
Cell Formulas
RangeFormula
G16G16=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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this

VBA Code:
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
 
Upvote 0
Try this

VBA Code:
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)
 
Upvote 0
In Sub timer, this line:
VBA Code:
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:
Code:
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:
Code:
Application.EnableEvents = False
'make changes
Application.EnableEvents = True
 
Upvote 0
Stephen, where do you suggest I put the
VBA Code:
 Application.EnableEvents = False

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

VBA 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 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.
 
Upvote 0
But this is my very first VBA code ever.
No problem, I could have made it clearer ....

Rich (BB code):
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
 
Upvote 0
Solution
No problem, I could have made it clearer ....

Rich (BB code):
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,223,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top