Time Delay not working...

[bapcki1]

Hi Everyone,

I can't seem to get a time delay to work. I thought it wouldn't be too difficult but all i want to do is: as part of a macros that is triggered by a calculation result equalling 1, at first cell A4 = 0 and then at the end of the macros, after a 10 second pause cell A4 = 95.

Why doesn't this code work! (ARRRGHH!!)

In Sheet 1

Option Explicit
 
Sub Wait(tSecs As Single)
     '   Timer to create a pause
    Dim sngSec As Single
     
    sngSec = Timer + tSecs
    Do While Timer < sngSec
        DoEvents
    Loop
End Sub

Private Sub Worksheet_Calculate()
If [K36] = 1 Then
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "0"
Range("A1").Copy
    Range("E1").PasteSpecial xlPasteValues
ThisWorkbook.Sheets("Sheet1").Range("K36").Value = "0"
Range("A1").ClearContents
Wait 10
ThisWorkbook.Sheets("Sheet1").Range("A4").Value = "95"
End If
End Sub

What happens is: Cell A4 does as it is supposed to but at the end of the macros instead of just deleting the contents of A1 (as it is supposed to) it also clears cell E1 but then the macros doesn't stop, I have to go into the VBA editor > run > break to stop it..????

Any help is greatly appreciated!

Thank you!

Paddy

 

[wigi]

For your Wait 10 procedure, I would use:

Application.Wait Now + TimeValue("0:0:10")

you might be running into problems since Wait is a built-in method (of the Application object).I am using it above, and most importantly, in a different way than you.

 

[bapcki1]

wigi = legend!

grazzi for all your help! -simple and it works as I want to, thanks again.


Paddy