Hi all

I am creating a 60 second countdown in XL2002 which currently uses the
system time and the wait function: Application.Wait (Now +
TimeValue("0:00:01")). After each second a cell value decreases (60 down to
0) and another cells Interior.Colorindex is changed.

This works well, but I need to be able to interupt the countdown to add
data, and then resume the countdown - something that can't be done using the
wait function.

I would like to interupt the countdown by pressing a button, which in turn
pops up a userform to add data to a table - (again, something that works but
not whilst the countdown is working!)

Is there another way of approaching this?

Thanks in advance!

Trevor

Maybe you could use the Application.OnTime function ??

--
Cheers
Nigel



"Trevor Williams" <(E-Mail Removed)> wrote in
message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
> Hi all
>
> I am creating a 60 second countdown in XL2002 which currently uses the
> system time and the wait function: Application.Wait (Now +
> TimeValue("0:00:01")). After each second a cell value decreases (60 down
> to
> 0) and another cells Interior.Colorindex is changed.
>
> This works well, but I need to be able to interupt the countdown to add
> data, and then resume the countdown - something that can't be done using
> the
> wait function.
>
> I would like to interupt the countdown by pressing a button, which in turn
> pops up a userform to add data to a table - (again, something that works
> but
> not whilst the countdown is working!)
>
> Is there another way of approaching this?
>
> Thanks in advance!
>
> Trevor
>
>

Hi Nigel,

I don't think the OnTime funtion will work as it waits for a pre-defined
time before it runs a procedure, and since I don't know when the user wants
to stop the countdown, I can't set it.

Is that right, or am I missing something?

Any other ideas?

Thanks

"Nigel" wrote:

> Maybe you could use the Application.OnTime function ??
>
> --
> Cheers
> Nigel
>
>
>
> "Trevor Williams" <(E-Mail Removed)> wrote in
> message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
> > Hi all
> >
> > I am creating a 60 second countdown in XL2002 which currently uses the
> > system time and the wait function: Application.Wait (Now +
> > TimeValue("0:00:01")). After each second a cell value decreases (60 down
> > to
> > 0) and another cells Interior.Colorindex is changed.
> >
> > This works well, but I need to be able to interupt the countdown to add
> > data, and then resume the countdown - something that can't be done using
> > the
> > wait function.
> >
> > I would like to interupt the countdown by pressing a button, which in turn
> > pops up a userform to add data to a table - (again, something that works
> > but
> > not whilst the countdown is working!)
> >
> > Is there another way of approaching this?
> >
> > Thanks in advance!
> >
> > Trevor
> >
> >
>
>
>

Trevor,
You can use one the many Timer classes available
http://vb.mvps.org/samples/project.asp?id=TimerObj

NickHK

"Trevor Williams" <(E-Mail Removed)> wrote in
message news:5D74C326-CF01-4A10-A1DA-(E-Mail Removed)...
> Hi Nigel,
>
> I don't think the OnTime funtion will work as it waits for a pre-defined
> time before it runs a procedure, and since I don't know when the user
wants
> to stop the countdown, I can't set it.
>
> Is that right, or am I missing something?
>
> Any other ideas?
>
> Thanks
>
> "Nigel" wrote:
>
> > Maybe you could use the Application.OnTime function ??
> >
> > --
> > Cheers
> > Nigel
> >
> >
> >
> > "Trevor Williams" <(E-Mail Removed)> wrote in
> > message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
> > > Hi all
> > >
> > > I am creating a 60 second countdown in XL2002 which currently uses the
> > > system time and the wait function: Application.Wait (Now +
> > > TimeValue("0:00:01")). After each second a cell value decreases (60
down
> > > to
> > > 0) and another cells Interior.Colorindex is changed.
> > >
> > > This works well, but I need to be able to interupt the countdown to
add
> > > data, and then resume the countdown - something that can't be done
using
> > > the
> > > wait function.
> > >
> > > I would like to interupt the countdown by pressing a button, which in
turn
> > > pops up a userform to add data to a table - (again, something that
works
> > > but
> > > not whilst the countdown is working!)
> > >
> > > Is there another way of approaching this?
> > >
> > > Thanks in advance!
> > >
> > > Trevor
> > >
> > >
> >
> >
> >

You can set the OnTime function to occur at some future point e.g. 1 secs.
This could trigger your cell count down, and then restart the ontime
event.again until the cell value reaches zero.

Whilst the ontime events are running you can interact with your workbook, so
a user action could cancel the ontime event, carry out the user actions and
then restart the ontime events.

This procedure gives you the countdown, the rest depends on what the user
interaction will be

Sub timerA1()
Application.OnTime Now + TimeValue("00:00:01"), "xTick"
End Sub

Sub xTick()
With Sheets("Sheet1")
.Range("A1") = .Range("A1") - 1
If .Range("A1") > 0 Then timerA1
End With
End Sub



--
Cheers
Nigel



"Trevor Williams" <(E-Mail Removed)> wrote in
message news:5D74C326-CF01-4A10-A1DA-(E-Mail Removed)...
> Hi Nigel,
>
> I don't think the OnTime funtion will work as it waits for a pre-defined
> time before it runs a procedure, and since I don't know when the user
> wants
> to stop the countdown, I can't set it.
>
> Is that right, or am I missing something?
>
> Any other ideas?
>
> Thanks
>
> "Nigel" wrote:
>
>> Maybe you could use the Application.OnTime function ??
>>
>> --
>> Cheers
>> Nigel
>>
>>
>>
>> "Trevor Williams" <(E-Mail Removed)> wrote in
>> message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
>> > Hi all
>> >
>> > I am creating a 60 second countdown in XL2002 which currently uses the
>> > system time and the wait function: Application.Wait (Now +
>> > TimeValue("0:00:01")). After each second a cell value decreases (60
>> > down
>> > to
>> > 0) and another cells Interior.Colorindex is changed.
>> >
>> > This works well, but I need to be able to interupt the countdown to add
>> > data, and then resume the countdown - something that can't be done
>> > using
>> > the
>> > wait function.
>> >
>> > I would like to interupt the countdown by pressing a button, which in
>> > turn
>> > pops up a userform to add data to a table - (again, something that
>> > works
>> > but
>> > not whilst the countdown is working!)
>> >
>> > Is there another way of approaching this?
>> >
>> > Thanks in advance!
>> >
>> > Trevor
>> >
>> >
>>
>>
>>

Excellent - thanks Nigel, I'll give that a go.

Trevor

"Nigel" wrote:

> You can set the OnTime function to occur at some future point e.g. 1 secs.
> This could trigger your cell count down, and then restart the ontime
> event.again until the cell value reaches zero.
>
> Whilst the ontime events are running you can interact with your workbook, so
> a user action could cancel the ontime event, carry out the user actions and
> then restart the ontime events.
>
> This procedure gives you the countdown, the rest depends on what the user
> interaction will be
>
> Sub timerA1()
> Application.OnTime Now + TimeValue("00:00:01"), "xTick"
> End Sub
>
> Sub xTick()
> With Sheets("Sheet1")
> .Range("A1") = .Range("A1") - 1
> If .Range("A1") > 0 Then timerA1
> End With
> End Sub
>
>
>
> --
> Cheers
> Nigel
>
>
>
> "Trevor Williams" <(E-Mail Removed)> wrote in
> message news:5D74C326-CF01-4A10-A1DA-(E-Mail Removed)...
> > Hi Nigel,
> >
> > I don't think the OnTime funtion will work as it waits for a pre-defined
> > time before it runs a procedure, and since I don't know when the user
> > wants
> > to stop the countdown, I can't set it.
> >
> > Is that right, or am I missing something?
> >
> > Any other ideas?
> >
> > Thanks
> >
> > "Nigel" wrote:
> >
> >> Maybe you could use the Application.OnTime function ??
> >>
> >> --
> >> Cheers
> >> Nigel
> >>
> >>
> >>
> >> "Trevor Williams" <(E-Mail Removed)> wrote in
> >> message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
> >> > Hi all
> >> >
> >> > I am creating a 60 second countdown in XL2002 which currently uses the
> >> > system time and the wait function: Application.Wait (Now +
> >> > TimeValue("0:00:01")). After each second a cell value decreases (60
> >> > down
> >> > to
> >> > 0) and another cells Interior.Colorindex is changed.
> >> >
> >> > This works well, but I need to be able to interupt the countdown to add
> >> > data, and then resume the countdown - something that can't be done
> >> > using
> >> > the
> >> > wait function.
> >> >
> >> > I would like to interupt the countdown by pressing a button, which in
> >> > turn
> >> > pops up a userform to add data to a table - (again, something that
> >> > works
> >> > but
> >> > not whilst the countdown is working!)
> >> >
> >> > Is there another way of approaching this?
> >> >
> >> > Thanks in advance!
> >> >
> >> > Trevor
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Use Ontime

Sub Countdown()
Static nCount As Long
Const CountTime As Long = 6 '0
If nCount = 0 Then
nCount = CountTime
Else
nCount = nCount - 1
End If
If nCount > 0 Then
Range("A1").Value = nCount
nTime = Now() + TimeSerial(0, 0, 1)
Debug.Print nTime
Application.OnTime nTime, "Countdown"
End If

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Trevor Williams" <(E-Mail Removed)> wrote in
message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
> Hi all
>
> I am creating a 60 second countdown in XL2002 which currently uses the
> system time and the wait function: Application.Wait (Now +
> TimeValue("0:00:01")). After each second a cell value decreases (60 down
to
> 0) and another cells Interior.Colorindex is changed.
>
> This works well, but I need to be able to interupt the countdown to add
> data, and then resume the countdown - something that can't be done using
the
> wait function.
>
> I would like to interupt the countdown by pressing a button, which in turn
> pops up a userform to add data to a table - (again, something that works
but
> not whilst the countdown is working!)
>
> Is there another way of approaching this?
>
> Thanks in advance!
>
> Trevor
>
>

trevor -
would you post your original timer code? sounds interesting!
thanks
susan

Hi Nigel - me again!

I've been dabbling with your code but just can't suss out where 'my code'
should go. Could you possibly point me in the right direction. I currently
have the following (using the old Application.Wait method).

(the Range("LEDS") is a series of cells that change from green to gray after
each second passes and Range("Clock") is the cell that has the countdown
number in it.)

Thanks!


Sub countdown()

Dim rng1 As Range
Dim Cell As Range

Set rng1 = Worksheets("Auction Room").Range("LEDS")

i = 0

For Each Cell In rng1

If Cell.Interior.ColorIndex = 51 Then
Range("Clock") = Range("Clock") - 1
Cell.Interior.ColorIndex = 38
Cell.Font.ColorIndex = 38
Application.Wait (Now + TimeValue("0:00:01"))
If Range("Clock") = 10 Then Range("Clock").Font.ColorIndex = 3
End If

i = i + 1

Next Cell

Range("LEDS").Font.ColorIndex = 0

End Sub

"Nigel" wrote:

> You can set the OnTime function to occur at some future point e.g. 1 secs.
> This could trigger your cell count down, and then restart the ontime
> event.again until the cell value reaches zero.
>
> Whilst the ontime events are running you can interact with your workbook, so
> a user action could cancel the ontime event, carry out the user actions and
> then restart the ontime events.
>
> This procedure gives you the countdown, the rest depends on what the user
> interaction will be
>
> Sub timerA1()
> Application.OnTime Now + TimeValue("00:00:01"), "xTick"
> End Sub
>
> Sub xTick()
> With Sheets("Sheet1")
> .Range("A1") = .Range("A1") - 1
> If .Range("A1") > 0 Then timerA1
> End With
> End Sub
>
>
>
> --
> Cheers
> Nigel
>
>
>
> "Trevor Williams" <(E-Mail Removed)> wrote in
> message news:5D74C326-CF01-4A10-A1DA-(E-Mail Removed)...
> > Hi Nigel,
> >
> > I don't think the OnTime funtion will work as it waits for a pre-defined
> > time before it runs a procedure, and since I don't know when the user
> > wants
> > to stop the countdown, I can't set it.
> >
> > Is that right, or am I missing something?
> >
> > Any other ideas?
> >
> > Thanks
> >
> > "Nigel" wrote:
> >
> >> Maybe you could use the Application.OnTime function ??
> >>
> >> --
> >> Cheers
> >> Nigel
> >>
> >>
> >>
> >> "Trevor Williams" <(E-Mail Removed)> wrote in
> >> message news:09BF68B5-2AD2-4DFF-AAC4-(E-Mail Removed)...
> >> > Hi all
> >> >
> >> > I am creating a 60 second countdown in XL2002 which currently uses the
> >> > system time and the wait function: Application.Wait (Now +
> >> > TimeValue("0:00:01")). After each second a cell value decreases (60
> >> > down
> >> > to
> >> > 0) and another cells Interior.Colorindex is changed.
> >> >
> >> > This works well, but I need to be able to interupt the countdown to add
> >> > data, and then resume the countdown - something that can't be done
> >> > using
> >> > the
> >> > wait function.
> >> >
> >> > I would like to interupt the countdown by pressing a button, which in
> >> > turn
> >> > pops up a userform to add data to a table - (again, something that
> >> > works
> >> > but
> >> > not whilst the countdown is working!)
> >> >
> >> > Is there another way of approaching this?
> >> >
> >> > Thanks in advance!
> >> >
> >> > Trevor
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Hi Susan

I've just posted my original code under my last reply to Nigel.

Trevor

"Susan" wrote:

> trevor -
> would you post your original timer code? sounds interesting!
> thanks
> susan
>
>