This code from a PowerPoint tips site should work just as well in Excel,
sometimes VBA is just VBA <g
http://www.pptfaq.com/FAQ00466.htm


"ALVESM" wrote:

that wont work i'll tell you why. I absolutely must have my macro sleeping
because if the macro is running, then the DDE links are not updated.

"Mike H" wrote:

Hi,

Put this couple of lines of code where you want to pause:-

Sub delayingtactics()
PauseTime = 2 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
Loop
Finish = Timer
End Sub

Mike

"ALVESM" wrote:

i want to have this macro sleeping 2s (during that time PXLAST will hopefully
get updated via DDE) until the condition is verified, can you help me out?
thanks in advance


Sub Macro1()

Dim i As Integer

For i = 1 To 1000
Do While Worksheets("sheet1").Range("pxlast").Value =
Worksheets("sheet1").Range("init").Offset(i - 1, 0).Value

Sleep 2000 / this wont work

Loop
Worksheets("sheet1").Range("pxlast").Copy
Worksheets("sheet1").Range("init").Offset(i, 0).PasteSpecial
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Next

End Sub