How to pause without being "modal"

[John]

Is there any way to have a routine pause but still allow the user to
use the worksheet during the pause? For example, I have a cell that
has its value changed from nothing to "Updating...". I want my routine
to make the font red for a brief period of time (eg., 3 seconds).

Currently, I can do this by calling SLEEP, but this makes the code
"modal"---users can't do anything until the routine ends. Any way to
pause without blocking the user, or is this a pie in the sky?




With Range("MyRange")

If .Value <> 1 Then
.Font.Bold = True
.Font.ColorIndex = 3 'red

Sleep 3000

.Font.Bold = False
.Font.ColorIndex = strColorIndex
End If

End With

 

[joeu2004]

Is there any way to have a routine pause but still allow
the user to use the worksheet during the pause?

Use Application.OnTime. Be sure to put the procedures (at least the OnTime
procedure) into a standard module (click on Insert, then Module in VBA), not
a worksheet module (right-click Excel tab, click on View Code).

Example....


Sub doit1()
With Sheets("sheet2").Range("a1")
.Value = Now
.NumberFormat = "m/dd/yyyy h:mm:ss"
.EntireColumn.AutoFit
End With
Application.OnTime Now + TimeSerial(0, 0, 3), "doit2"
End Sub

Sub doit2()
With Sheets("sheet2").Range("a2")
.Value = Now
.NumberFormat = "m/dd/yyyy h:mm:ss"
End With
End Sub