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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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

Unlike Application.Wait where the next code step is only executed after the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate a
procedure at the end of the OnTime . Your sequencing code is currently
dependent on this delay. So in its current form will not work with OnTime.

You will need to change the way the LEDs sequence, from the first to the
last cell, keeping track of which is the next cell to act upon and decrement
your clock counter cell. This code would be triggered by the OnTime event
time out. As the timer is running independent of the code, the user can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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

OK - thanks again Nigel. I'll give it a go!

Trevor

"Nigel" wrote:

Unlike Application.Wait where the next code step is only executed after the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate a
procedure at the end of the OnTime . Your sequencing code is currently
dependent on this delay. So in its current form will not work with OnTime.

You will need to change the way the LEDs sequence, from the first to the
last cell, keeping track of which is the next cell to act upon and decrement
your clock counter cell. This code would be triggered by the OnTime event
time out. As the timer is running independent of the code, the user can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote in
message ...
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" wrote in
message ...
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

A couple of assumptions here... the range LEDS has 60 columns and the count
always starts at 60, my colors are red and green but can be changed of
course!

Sub StartTimer()
' reset the clock and the displays at start
If Range("Clock") = 0 Then
Range("Clock") = 60
Range("LEDS").Interior.Color = vbGreen
Range("Clock").Interior.Color = vbWhite
End If
Application.OnTime Now + TimeValue("00:00:01"), "xTick"
End Sub

Sub xTick()
Dim xLED As Integer
xLED = 60
Range("Clock") = Range("Clock") - 1
Range("LEDS").Cells(1, xLED - Range("Clock")).Interior.Color = vbRed
If Range("Clock") 0 Then StartTimer
If Range("Clock") <= 10 Then Range("Clock").Interior.Color = vbRed
End Sub

--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
OK - thanks again Nigel. I'll give it a go!

Trevor

"Nigel" wrote:

Unlike Application.Wait where the next code step is only executed after
the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate a
procedure at the end of the OnTime . Your sequencing code is currently
dependent on this delay. So in its current form will not work with
OnTime.

You will need to change the way the LEDs sequence, from the first to the
last cell, keeping track of which is the next cell to act upon and
decrement
your clock counter cell. This code would be triggered by the OnTime
event
time out. As the timer is running independent of the code, the user can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of
which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote in
message ...
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" wrote
in
message ...
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

Hello Nigel.

Thanks for your code yesterday - and you 'assumed' right.

Unfortunately the code doesn't stop counting down when a userform is
displayed. The form is set to modal. Is there anything else I should be
checking to stop the code?

Thanks

Trevor

"Nigel" wrote:

A couple of assumptions here... the range LEDS has 60 columns and the count
always starts at 60, my colors are red and green but can be changed of
course!

Sub StartTimer()
' reset the clock and the displays at start
If Range("Clock") = 0 Then
Range("Clock") = 60
Range("LEDS").Interior.Color = vbGreen
Range("Clock").Interior.Color = vbWhite
End If
Application.OnTime Now + TimeValue("00:00:01"), "xTick"
End Sub

Sub xTick()
Dim xLED As Integer
xLED = 60
Range("Clock") = Range("Clock") - 1
Range("LEDS").Cells(1, xLED - Range("Clock")).Interior.Color = vbRed
If Range("Clock") 0 Then StartTimer
If Range("Clock") <= 10 Then Range("Clock").Interior.Color = vbRed
End Sub

--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
OK - thanks again Nigel. I'll give it a go!

Trevor

"Nigel" wrote:

Unlike Application.Wait where the next code step is only executed after
the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate a
procedure at the end of the OnTime . Your sequencing code is currently
dependent on this delay. So in its current form will not work with
OnTime.

You will need to change the way the LEDs sequence, from the first to the
last cell, keeping track of which is the next cell to act upon and
decrement
your clock counter cell. This code would be triggered by the OnTime
event
time out. As the timer is running independent of the code, the user can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of
which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote in
message ...
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" wrote
in
message ...
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

I would include a switch (Boolean) to control the StartTimer code, this can
be switched on/off as required. The variable would need to be Public so it
was persistent in all code.

So at top of your master Module add

Public TimerOff as Boolean

Replace the code in the StartTimer to include

If Not TimerOff Then Application.OnTime Now + TimeValue("00:00:01"),
"xTick"

Whenever you wish to stop the timer set the var TimerOff to True (it
defaults to False when initialised)

So in your code that shows the userform add the above or even add it in the
userform.initialize event, you could reset the TimerOff var to False when
the form is deactivated / unloaded.

Good luck!


--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
Hello Nigel.

Thanks for your code yesterday - and you 'assumed' right.

Unfortunately the code doesn't stop counting down when a userform is
displayed. The form is set to modal. Is there anything else I should be
checking to stop the code?

Thanks

Trevor

"Nigel" wrote:

A couple of assumptions here... the range LEDS has 60 columns and the
count
always starts at 60, my colors are red and green but can be changed of
course!

Sub StartTimer()
' reset the clock and the displays at start
If Range("Clock") = 0 Then
Range("Clock") = 60
Range("LEDS").Interior.Color = vbGreen
Range("Clock").Interior.Color = vbWhite
End If
Application.OnTime Now + TimeValue("00:00:01"), "xTick"
End Sub

Sub xTick()
Dim xLED As Integer
xLED = 60
Range("Clock") = Range("Clock") - 1
Range("LEDS").Cells(1, xLED - Range("Clock")).Interior.Color = vbRed
If Range("Clock") 0 Then StartTimer
If Range("Clock") <= 10 Then Range("Clock").Interior.Color = vbRed
End Sub

--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
OK - thanks again Nigel. I'll give it a go!

Trevor

"Nigel" wrote:

Unlike Application.Wait where the next code step is only executed
after
the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate
a
procedure at the end of the OnTime . Your sequencing code is
currently
dependent on this delay. So in its current form will not work with
OnTime.

You will need to change the way the LEDs sequence, from the first to
the
last cell, keeping track of which is the next cell to act upon and
decrement
your clock counter cell. This code would be triggered by the OnTime
event
time out. As the timer is running independent of the code, the user
can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of
which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote
in
message ...
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"
wrote
in
message
...
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

OK... I'll let you know...

Trevor

"Nigel" wrote:

I would include a switch (Boolean) to control the StartTimer code, this can
be switched on/off as required. The variable would need to be Public so it
was persistent in all code.

So at top of your master Module add

Public TimerOff as Boolean

Replace the code in the StartTimer to include

If Not TimerOff Then Application.OnTime Now + TimeValue("00:00:01"),
"xTick"

Whenever you wish to stop the timer set the var TimerOff to True (it
defaults to False when initialised)

So in your code that shows the userform add the above or even add it in the
userform.initialize event, you could reset the TimerOff var to False when
the form is deactivated / unloaded.

Good luck!


--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
Hello Nigel.

Thanks for your code yesterday - and you 'assumed' right.

Unfortunately the code doesn't stop counting down when a userform is
displayed. The form is set to modal. Is there anything else I should be
checking to stop the code?

Thanks

Trevor

"Nigel" wrote:

A couple of assumptions here... the range LEDS has 60 columns and the
count
always starts at 60, my colors are red and green but can be changed of
course!

Sub StartTimer()
' reset the clock and the displays at start
If Range("Clock") = 0 Then
Range("Clock") = 60
Range("LEDS").Interior.Color = vbGreen
Range("Clock").Interior.Color = vbWhite
End If
Application.OnTime Now + TimeValue("00:00:01"), "xTick"
End Sub

Sub xTick()
Dim xLED As Integer
xLED = 60
Range("Clock") = Range("Clock") - 1
Range("LEDS").Cells(1, xLED - Range("Clock")).Interior.Color = vbRed
If Range("Clock") 0 Then StartTimer
If Range("Clock") <= 10 Then Range("Clock").Interior.Color = vbRed
End Sub

--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
OK - thanks again Nigel. I'll give it a go!

Trevor

"Nigel" wrote:

Unlike Application.Wait where the next code step is only executed
after
the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate
a
procedure at the end of the OnTime . Your sequencing code is
currently
dependent on this delay. So in its current form will not work with
OnTime.

You will need to change the way the LEDs sequence, from the first to
the
last cell, keeping track of which is the next cell to act upon and
decrement
your clock counter cell. This code would be triggered by the OnTime
event
time out. As the timer is running independent of the code, the user
can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of
which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote
in
message ...
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"
wrote
in
message
...
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

Wahoo! - It works a treat. I switched it off when the userform activated and
switched it on again from a button that closes the form. I had to recall the
StartTimer procedure too when the form closes.

Thank's a lot for your help on this Nigel - Now onto the next problem...

Trevor

"Nigel" wrote:

I would include a switch (Boolean) to control the StartTimer code, this can
be switched on/off as required. The variable would need to be Public so it
was persistent in all code.

So at top of your master Module add

Public TimerOff as Boolean

Replace the code in the StartTimer to include

If Not TimerOff Then Application.OnTime Now + TimeValue("00:00:01"),
"xTick"

Whenever you wish to stop the timer set the var TimerOff to True (it
defaults to False when initialised)

So in your code that shows the userform add the above or even add it in the
userform.initialize event, you could reset the TimerOff var to False when
the form is deactivated / unloaded.

Good luck!


--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
Hello Nigel.

Thanks for your code yesterday - and you 'assumed' right.

Unfortunately the code doesn't stop counting down when a userform is
displayed. The form is set to modal. Is there anything else I should be
checking to stop the code?

Thanks

Trevor

"Nigel" wrote:

A couple of assumptions here... the range LEDS has 60 columns and the
count
always starts at 60, my colors are red and green but can be changed of
course!

Sub StartTimer()
' reset the clock and the displays at start
If Range("Clock") = 0 Then
Range("Clock") = 60
Range("LEDS").Interior.Color = vbGreen
Range("Clock").Interior.Color = vbWhite
End If
Application.OnTime Now + TimeValue("00:00:01"), "xTick"
End Sub

Sub xTick()
Dim xLED As Integer
xLED = 60
Range("Clock") = Range("Clock") - 1
Range("LEDS").Cells(1, xLED - Range("Clock")).Interior.Color = vbRed
If Range("Clock") 0 Then StartTimer
If Range("Clock") <= 10 Then Range("Clock").Interior.Color = vbRed
End Sub

--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
OK - thanks again Nigel. I'll give it a go!

Trevor

"Nigel" wrote:

Unlike Application.Wait where the next code step is only executed
after
the
waiting time, Application.OnTime, triggers a time function that starts
independently and does not cause your code to halt, but will initiate
a
procedure at the end of the OnTime . Your sequencing code is
currently
dependent on this delay. So in its current form will not work with
OnTime.

You will need to change the way the LEDs sequence, from the first to
the
last cell, keeping track of which is the next cell to act upon and
decrement
your clock counter cell. This code would be triggered by the OnTime
event
time out. As the timer is running independent of the code, the user
can
interact with the worksheet.

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

Sub xTick()
Range("Clock") = Range("Clock") - 1
If Range("Clock") 0 Then StartTimer

' your code to control the leds goes here adapted to keep track of
which
led to act upon


End Sub



--
Cheers
Nigel



"Trevor Williams" wrote in
message ...
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" wrote
in
message ...
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"
wrote
in
message
...
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, all,
This timer totaly stops counting when I start typing on a cell.
Is there a way to keep it running, at least on the background?

Thanks,
Davey

"Bob Phillips" wrote:

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" wrote in
message ...
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

VBA doesn't run when you're in cell edit mode.

Tim


"Davey" wrote in message
...
Hi, all,
This timer totaly stops counting when I start typing on a cell.
Is there a way to keep it running, at least on the background?

Thanks,
Davey

"Bob Phillips" wrote:

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" wrote in
message ...
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