Using countdown timer to trigger macro

[cic]

Could anyone tell me how i could use the countdown timer to trigger a macro. I have been messing around with change event though haven't succeeded. I would like for the macro named Solution to be triggered when the clock hits 00:01:05. Even a point in the right direction would be appreciated. Thanks in advance

Regards
Dan

[Captain Sensible]

You could stick the routine within a Worksheet_Calculate() or Worksheet_Change depending on how your sheets currently coded.

Something as simple as

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
 
 
  If Range("D2").Value = TimeValue("00:01:05") Then
     Call Solution
  End If
 
 

 
End Sub

would work but I'd advise you to stick in additional flags to log the fact it's fired so your Macro Solution doesn't get triggered after each refresh. It's sometimes worth sticking in a range of times rather than an exact time as slow refreshes etc may mean you miss an exact time slot i.e.

If Range("D2").Value < TimeValue("00:01:05") And Range("D2").Value > TimeValue("00:00:55")Then
Call Solution
End If

[Captain Sensible]

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
 
 
 
If Range("D2").Value < TimeValue("00:01:05") And Range("D2").Value > TimeValue("00:00:55") And Range("T1").Value <> "Fired" Then
     Call Solution
     Range("T1").Value = "Fired"
  End If
 
 

 
End Sub

The above would add a simple flag just by placing Fired in cell T1 to ensure you didn't loop Solution continually, you could then clear that either thru your other code or manually. Obviously you can change the time parameters to whatever you wanted

[cic]

Sorry been away, thanks for the replies will try them out now and let you know how i go. thanks again

Regards
Dan

[cic]

Worked beautifully thanks for your help. Much appreciated.

Regards
Dan

[dixiedean]

Hi

I tried this and it works a treat except i cant stop my macro from repeating, any ideas on how to do this please?

Thanks

D

[dixiedean]

Private Sub Worksheet_Change(ByVal Target As Range)



If Range("D2").Value < TimeValue("00:01:05") And Range("D2").Value > TimeValue("00:00:55") And Range("T1").Value <> "Fired" Then
Call Solution
Range("T1").Value = "Fired"
End If




End Sub

How would i modify this to reset the fired cell at say time -00:01:00?

Thanks

D

[Captain Sensible]

It gets a bit awkward once the time goes negative as excel treats it as text rather than time.

Not sure why you'd want to use -00:01:00 though, that would assume the market is now in play but because start times rarely go off on time they'd be much better ways to do things if you wanted to reset after the market had been off/inplay for a minute.