Help with comparing timer with static time

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
[TABLE="width: 310"]
<tbody>[TR]
[TD]I wonder if any members of this forum could kindly help me with this problem?[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]In my spreadsheet, cell DD4 has a countdown timer running, linked to a website, and the timer runs down to zero 00:00:00, and then continues past zero with a negative sign, e.g. -00:01:00 after one minute, and so on.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]What I am trying to achieve is to compare this timer's value with a fixed static value in cell DD3, that value will be 00:01:00. When both values are momentarily equal I want to call two macros, Macro1, Macro2.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Can this be done with this kind of statement in VBA, or is there another way, perhaps with using TimeValue formula?[/TD]
[/TR]
[TR]
[TD]I have tried fiddling with this code on Workbook Open[/TD]
[/TR]
[TR]
[TD]Private Sub Workbook_Open()

Application.OnTime TimeValue(Range("DD4").Text = Range("DD3").Text), "Macro1"

Application.OnTime TimeValue(Range("DD4").Text = Range("DD3").Text), "Macro2"

End Sub
<strike></strike>[/TD]
[/TR]
[TR]
[TD]<strike></strike>[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]But how to compare the value in the timer to the fixed value in DD3? Both macros need to run automatically when the two time values are equal.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]I am always very appreciative of the help from the knowledgeable members of this forum.[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Regards,[/TD]
[/TR]
[TR]
[TD]Vern[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Try something along these lines
- assumes that DD4 is TIME (ie not date and time displayed as time)

In Standard Module
Code:
Sub StartTimer()
    Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), procedure:="CompareTime", schedule:=True
End Sub

Sub StopTimer()
   On Error Resume Next
   Application.OnTime earliesttime:=Now + TimeSerial(0, 0, 1), procedure:="CompareTime", schedule:=False
End Sub

Sub CompareTime()
    If (Time + 1/24)  >= Sheets("[COLOR=#ff0000]SheetName[/COLOR]").Range("DD4") Then
        MsgBox "True"
        StopTimer
    Else
        Call StartTimer
    End If
End Sub

In ThisWorkbook module
Code:
Private Sub Workbook_Open()
    Call CompareTime
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub
 
Last edited:
Upvote 0
Oops I forgot to change something after testing! :oops::oops:
- this is wrong
Code:
 If (Time + 1/24)  >= Sheets("SheetName").Range("DD4") Then
The test should be the value in your static cell , perhaps....
Code:
If Sheets("SheetName").Range("DD3")  >= Sheets("SheetName").Range("DD4") Then
 
Last edited:
Upvote 0
Thanks Yongle, just trying it out, seems to fall down on the code you posted in your second message. Doesn't like the reference to the sheets in a standard module?

I'll try and fix it.
Regards,
Vern
 
Upvote 0
What is the name of your sheet? You need to replace SheetName with whatever your sheet is called
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,171
Members
453,021
Latest member
Justyna P

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