Delay command in macro
Question
First, thank you for your previous help. How would I put a delay command into a macro function? My goal is to use the loop information that you recently gave me along with a delay (or pause) command to create a message to the user that will flash (by changing the "format cells" and "font color" for a few cycles.
Answer
Art,
You can use the Wait command.
here are some other approaches.
Chip Pearson shows a way to do it using the Ontime command
http://www.cpearson.com/excel/BlinkingText.aspx
He blinks text, but you can use the same code to blink the interior of the cell.
this person used a do loop to continue to loop (doing nothing) until a set time is reached
Private Sub Flash_Cells()
Dim FlashColor As Integer Dim MakeFlash As Range Dim x As Integer Dim TheSpeed Dim i
'Just a random range of cells. Change it to whatever you want. Set MakeFlash = Range("A1,C6,F3,H4")
For Each i In MakeFlash
If i.Value > 4 Then
FlashColor = 3 'Set the color to red
'Make the cell range flash fast: 0.01 to slow: 0.99 TheSpeed = 0.2
'Flash 7 times Do Until x = 7
DoEvents Start = Timer Delay = Start + TheSpeed Do Until Timer > Delay DoEvents MakeFlash.Interior.ColorIndex = FlashColor Loop Start = Timer Delay = Start + TheSpeed Do Until Timer > Delay DoEvents MakeFlash.Interior.ColorIndex = xlNone Loop x = x + 1 Loop
End If Next i
End Sub
this guy uses the window API to call the sleep command
Advertisement