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.

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.BackColor = Red
> ' MsgBox ("")
> x = Timer + 2
> Do
> Loop Until Timer > x
> Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Gray
>
> Thanks for any suggestions.
> Bert.
>
>
>

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.BackColor = Red
> > ' MsgBox ("")
> > x = Timer + 2
> > Do
> > Loop Until Timer > x
> > Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Gray
> >
> > Thanks for any suggestions.
> > Bert.
> >
> >
> >

=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.BackColor = Red
> > > ' MsgBox ("")
> > > x = Timer + 2
> > > Do
> > > Loop Until Timer > x
> > > Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Gray
> > >
> > > Thanks for any suggestions.
> > > Bert.
> > >
> > >
> > >

--

Dave Peterson

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.BackColor = Red
> > > > ' MsgBox ("")
> > > > x = Timer + 2
> > > > Do
> > > > Loop Until Timer > x
> > > > Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Gray
> > > >
> > > > Thanks for any suggestions.
> > > > Bert.
> > > >
> > > >
> > > >
>
> --
>
> Dave Peterson
>

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.BackColor = Red
> ' MsgBox ("")
> x = Timer + 2
> Do
> Loop Until Timer > x
> Worksheets(2).OLEObjects.Item(BtnNo).Object.BackColor = Gray
>
> Thanks for any suggestions.
> Bert.
>
>
>