Call macro on change if two time values are equal

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
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.

Code:
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


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

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

Code:
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

Code:
Private Sub Workbook_Open()
    Call CompareTime
End Sub

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

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

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
Sorry, 'End Select' is not meant to be in the Worksheet Change code I posted.
Vern
 

MrCaptain

Board Regular
Joined
Dec 5, 2006
Messages
123
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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
Top