Triggering Macro to Run Once When Countdown Timer is Within Range

[rob51852]

Hi,

I have put together the following macro to run when a countdown timer is between a certain range. I only want it to run the Capture macro once.

Is this a good way to code it or can anyone see any potential issues?

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("AG5") < 12650 And Range("AG5") > 12640 Then Call Capture
Application.EnableEvents = True
End Sub

Thanks

 

[dmt32]

Hi,
try this update to your code & see if does what you want

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    
    With Range("AG5")
        If Val(.ID) = 0 Then
            If .Value < 12650 And .Value > 12640 Then Call Capture
        Else
            .ID = xlOff
        End If
    End With
    
    Application.EnableEvents = True
End Sub

Solution should re-set itself each time workbook is closed.

Dave

 

[Yongle]

@dmt32 - clever use of Range ID property
- I would not have thought of that

But it does not trigger correctly for me
Can I suggest this modification

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Range("AG5")
        If Val(.ID) <> xlOff Then
            If .Value < 12650 And .Value > 12640 Then
                .ID = xlOff
                Call Capture
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub

 

[dmt32]

@dmt32 - clever use of Range ID property
- I would not have thought of that

But it does not trigger correctly for me
Can I suggest this modification

Most of the offerings I post are untested & welcome any helpful corrections
I have used ID of Range as "Tag" in many situations like this - can be very handy but not sure if OP wanted the run once to be permanent as ID property as you know, does not retain its value.

Dave