Call macro on change if two time values are equal

[MrCaptain]

Hi all,

Thanks to some super coding by member Yongle I have been able to call a macro based on comparing two time values in adjacent cells.

However, I now realise that running it after workbook open means I cannot change the static value in cell DD2 and get a new comparison with the running timer in cell DD3 without closing and reopening the workbook.

So I would like to use perhaps Worksheet Change, when cell DD2 value is changed and its value is equal to DD3 then call two macros. DD2 could change by manual input or from a formula elsewhere. I am wary of Worksheet Change after a few crashes meant I could not access my excel file and had to start again. However I always have a backup of the workbook.

From what I have read it seems I need to declare a public variable in the macro and use 'run "macroname"' in the worksheet change but also include error trapping and enable/disable events to avoid continuous loops?

I dont know how to use the public variable x.Target=Target in the Worksheet Change and macros to achieve this.

Here's the Worksheet Change code I have cobbled together which doesn't fire the macro at present.
Is it ok for the time vaue comparison to be momentary i.e. when both values are equal time for 1 second? I have used cell DDE2 to create a 1 for true or 0 for false with =IF($DD$2=$DD$3,1,0) and that is therefore my target to fire the macro "CompareTime" when $DDE$2 = 1.

Private Sub Worksheet_Change(ByVal Target As Range)
   'Do nothing if more than one cell is changed or content deleted
   If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    
    If Target.Address = "$DE$2" Then
       'Ensure target is a number
        If IsNumeric(Target) Then
            'Stop any possible runtime errors and halting code
            On Error Resume Next
                'Turn off ALL events so the Target does not _
                 'put the code into a loop.
                Application.EnableEvents = False
                Call "CompareTime"
                End Select
                'Turn events back on
                Application.EnableEvents = True
            'Allow run time errors again
            On Error GoTo 0
        End If
End If
End Sub

And here's the code produced by Yongle which works with Workbook Open

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()
ThisWorkbook.Sheets("Linked").Activate
    If ActiveSheet.Range("DD2").Value >= ActiveSheet.Range("DD3").Value Then
        Call Macro1
        Call Macro2
       Call StopTimer
    Else
        Call StartTimer
    End If
End Sub

Private Sub Workbook_Open()
    Call CompareTime
End Sub

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

Thank you for any help you can provide.
Regards,
Vern

 

[MrCaptain]

Sorry, 'End Select' is not meant to be in the Worksheet Change code I posted.
Vern

 

[MrCaptain]

Oh dear, another error from me, DDE2 should be DE2.
Sorry if that's causing any confusion in my attempt to explain what I am trying to achieve.
Vern