How to pause without being "modal"

  • Thread starter Thread starter John
  • Start date Start date
J

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
 
John said:
Is there any way to have a routine pause but still allow
the user to use the worksheet during the pause?
Click to expand...

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
 
Back
Top