Help with comparing timer with static time

[MrCaptain]

[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]

 

[Yongle]

Try something along these lines
- assumes that DD4 is TIME (ie not date and time displayed as time)

In Standard Module

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

Private Sub Workbook_Open()
    Call CompareTime
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

 

[Yongle]

Oops I forgot to change something after testing!
- this is wrong

 If (Time + 1/24)  >= Sheets("SheetName").Range("DD4") Then

The test should be the value in your static cell , perhaps....

If Sheets("SheetName").Range("DD3")  >= Sheets("SheetName").Range("DD4") Then

 

[MrCaptain]

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

 

[Yongle]

What is the name of your sheet? You need to replace SheetName with whatever your sheet is called

 

[MrCaptain]

Hi Yongle,
Its now working, couldn't have done it without you, many thanks.
Regards,
Vern

 

[Yongle]

good - thanks for your feedback