Fire vba when time hits ....

[mikedmc]

Hi,

I would appreciate a little help with my vba. Currently I have been able to have the appropriate vba fire when required but due to refresh times or when I have more than 1 mkt being watched the timer sometimes misses the time required therefore the vba isn't fired. This is my current vba.

Code: Select all

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

Select Case Worksheets("Mkt3").Range("D12").Value

           Case TimeValue("00:15:00")
              Worksheets("Mkt3").Range("AC15:AC60").Value = ""
              Worksheets("Mkt3").Range("AE15:AE60").Value = ""
              Worksheets("Mkt3").Range("AC15:AC60").Value = Worksheets("Mkt3").Range("AA15:AA60").Value
             
          Case TimeValue("00:02:00")

              Worksheets("Mkt3").Range("AE15:AE60").Value = Worksheets("Mkt3").Range("AA15:AA60").Value

    End Select

Application.EnableEvents = True


Works fine except when it misses the time (D12 is by the way is D2 if u start in A1). Is there an easy way to have this fire at the above times without it ever missing the time or have a little leway put in that will allow for the odd miss?

I was thinking of having it fire between 2 times e.g. for the 00:15:00, have it fire between 00:15:00 and 00:14:55 but then it continues 2 fire until it reaches 00:14:55, I would prefer it 2 fire just the once.

Any suggestions, tips, advice as always appreciated. Thank you.

[alrodopial]

I was thinking of having it fire between 2 times e.g. for the 00:15:00, have it fire between 00:15:00 and 00:14:55 but then it continues 2 fire until it reaches 00:14:55, I would prefer it 2 fire just the once.

Any suggestions, tips, advice as always appreciated. Thank you.

Inside the VBA and after the point you want place something like this:
Range("AA1")="DONE"
so you create a flag that tells you that your macro has been fired.
Combine it with something like this : If Range("AA1")="DONE" then Exit Sub.

Before next race you have to clear the AA1.

[mikedmc]

Thank you alrodopial, that's how I did it but was hoping there was a better way, or at least a better way of getting it done at the right time.

One more think I use Timevalue("00:15:00") but in my case select statement, Timevalue (00:15:00) To Timevalue(00:15:02) doesn't work, I had to do Timevalue(00:15:00), Timevalue("00:15:01") etc. etc.

Again is there a better way?

Thank you.

[dgs2001]

Mikedmc

Not sure its better, but in order to not miss your time slot you could use the Worksheet calculate event combined with the "Done" flag in AA1.

Set your code to check for the "Done" flag every "worksheet calculate" after time value < 15, and if its not "Done" then do it and set the flag.

Duncan