Delay command in macro

Last Edited By Krjb Donovan
Last Updated: Mar 05, 2014 09:24 PM GMT

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

http://www.vbforums.com/archive/index.php/t-326960.html

Advertisement

©2020 eLuminary LLC. All rights reserved.