Revised Time Count-Down

Yogi

Board Regular
Joined
Oct 21, 2002
Messages
74
I want to be able to press a macro button that initiates a count down from 25-0 seconds in cell B2. Is this possible?
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Yogi,

Is this what you want?
<pre>Sub CountDown()
Dim pausetime As Single
Dim start As Single
Dim finish As Single
Dim totaltime As Single

pausetime = 25 ' Set duration.
start = Timer ' Set start time.

Do While Timer < start + pausetime
DoEvents ' Yield to other processes.
Sheets(1).Range("B2").Value = _
Format(pausetime + (start - Timer), "##")
Loop

finish = Timer ' Set end time.
totaltime = finish - start
End

End Sub</pre>
HTH
 

Yogi

Board Regular
Joined
Oct 21, 2002
Messages
74
Richie,
It works great! Thanks!
Is there any way I could write the macro to display the "25" seconds before the button is pushed starting the countdown and then after the countdown is completed it goes back to displaying the "25" seconds until the button is pushed again?
Thanks again.
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
On 2002-10-25 17:45, Yogi wrote:
Richie,
It works great! Thanks!
Is there any way I could write the macro to display the "25" seconds before the button is pushed starting the countdown and then after the countdown is completed it goes back to displaying the "25" seconds until the button is pushed again?
Thanks again.

Just change to the following:

<pre/>
Sub CountDown()
Dim pausetime As Single
Dim start As Single
Dim finish As Single
Dim totaltime As Single

pausetime = 25 ' Set duration.
start = Timer ' Set start time.
Sheets(1).Range("B2").Value = 25
Do While Timer < start + pausetime
DoEvents ' Yield to other processes.
Sheets(1).Range("B2").Value = _
Format(pausetime + (start - Timer), "##")
Loop

Sheets(1).Range("B2").Value = 25

End Sub
</pre>

Note: Whilst Do Events yields to other processes you will notice 2 things;
1) There is a flicker
2) Editing cells will stop the operating macro.

There are a number of ways to over come this;
1) Use an ActiveX control timer
2) Use an Ontile call
3) Use some API calls to the winmm dll
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636

ADVERTISEMENT

I would just use the timer control and set the interval to 1000:

Private Sub Workbook_Open()
Sheets("sheet1").IeTimer1.Interval = 1000
End Sub


Private Sub IeTimer1_Timer()
if cTime<= 0 then
ietimer.interval = 0
else
range("yourcell").value = cTick
end if
module1.ctick=module1.cTick - 1
End Sub


In the general decs of module1:

Dim cTick As Integer
cTick = 25

Oops, there :)
This message was edited by zacemmel on 2002-10-25 19:23
 

Ivan F Moala

MrExcel MVP
Joined
Feb 10, 2002
Messages
4,209
I don't think your code is complete ?
You will need to Disable the Timer control
to stop the Timer from always updating ?
 

Yogi

Board Regular
Joined
Oct 21, 2002
Messages
74

ADVERTISEMENT

Thanks again to the Excel community for all of the help.
 

Yogi

Board Regular
Joined
Oct 21, 2002
Messages
74
The 25 second display still flickers after the countdown is complete. I can live with it but would prefer it didn't do this.
Thanks
 

Wil Moosa

Well-known Member
Joined
Aug 11, 2002
Messages
882
Looking at Ivan's advise from the 25th.

Sub CountDown()
Dim pausetime As Single
Dim start As Single
Dim finish As Single
Dim totaltime As Single

pausetime = 25 ' Set duration.
start = Timer ' Set start time.

Do While Timer < start + pausetime
DoEvents ' Yield to other processes.
Sheets(1).Range("B2").Value = _
Format(pausetime + (start - Timer), "##")
Loop

finish = Timer ' Set end time.
totaltime = finish - start
End

End Sub


I want the count down followed by some action (a macro getting started). Where do I add Application.Run "Alfabet_A_I"
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Sub CountDown()
Dim pausetime As Single
Dim start As Single
Dim finish As Single
Dim totaltime As Single

pausetime = 25 ' Set duration.
start = Timer ' Set start time.

Do While Timer < start + pausetime
DoEvents ' Yield to other processes.
Sheets(1).Range("B2").Value = _
Format(pausetime + (start - Timer), "##")
Loop

finish = Timer ' Set end time.
totaltime = finish - start

MsgBox "All Done!" '<== Your "Sub" name here, inplace of the MsgBox!

End
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,304
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top