do...loop with timer problem
 

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.BackCo lor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackCo lor = Gray

Thanks for any suggestions.
Bert.

do...loop with timer problem
 

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

"Bert" wrote:

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.BackCo lor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackCo lor = Gray

Thanks for any suggestions.
Bert.

do...loop with timer problem
 

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

posted before proof reading.
FSt1

"FSt1" wrote:

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

"Bert" wrote:

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.BackCo lor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackCo lor = Gray

Thanks for any suggestions.
Bert.

do...loop with timer problem
 

=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 wrote:

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

posted before proof reading.
FSt1

"FSt1" wrote:

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

"Bert" wrote:

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.BackCo lor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackCo lor = Gray

Thanks for any suggestions.
Bert.




--

Dave Peterson

do...loop with timer problem
 

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

"Dave Peterson" wrote:

=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 wrote:

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

posted before proof reading.
FSt1

"FSt1" wrote:

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

"Bert" wrote:

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.BackCo lor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackCo lor = Gray

Thanks for any suggestions.
Bert.




--

Dave Peterson

do...loop with timer problem
 

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

"Bert" wrote:

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.BackCo lor = Red
' MsgBox ("")
x = Timer + 2
Do
Loop Until Timer x
Worksheets(2).OLEObjects.Item(BtnNo).Object.BackCo lor = Gray

Thanks for any suggestions.
Bert.