do...loop with timer problem

[Bert]

The following code (in Excel 2003) changes a command button's color, then
pauses for a couple of seconds, and changes the color again. The problem is
the change BEFORE the loop never displays. Is there something else I should
be doing to make this work? (IF I comment out the delay loop and use the
msgbox, the first color change shows just fine.)

Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer > x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Gray

Thanks for any suggestions.
Bert.

 

[FSt1]

hi,
not sure but this might be a better way to cause a short pause

Application.Wait (Now + TimeValue("0:00:02"))

i have never seen using a do loop to pause code and written the way you have
it, there is nothing inside the loop to increment x. then maybe you left out
some code.

as to the other problem, you might try adding a DoEvents just after the
color change. maybe before too.
look up DoEvents in vb help.

Regards
FSt1

 

[FSt1]

sorry. that should be....
Application.Wait (Now() + TimeValue("0:00:02"))

posted before proof reading.
FSt1

 

[Dave Peterson]

=NOW() is an excel worksheet function.
Now is a VBA function. It doesn't need the ()'s (but forgives you if you
include them)

In fact, your code doesn't need the outside parens:
Application.Wait Now + TimeValue("0:00:02")

Personally, I'd use timeserial instead of timevalue:
Application.Wait Now + Timeserial(0,0,2)

 

[FSt1]

hi
thinks for the info. i had always use it as i posted. and it works. but now
i know how to shorten it a tad.

Thanks
FSt1

 

[JLGWhiz]

I couldn't get the OLEObjects from the Forms tool bar to work, but using the
Controls Toolbox button, this syntax works.

Sub btnClr()
Worksheets(1).CommandButton1.BackColor = RGB(0, 255, 255)
' MsgBox ("")
s = Timer + 2
Do While Timer < s
DoEvents
Loop
Worksheets(1).CommandButton1.BackColor = RGB(255, 100, 255)
End Sub