Userform - Countdown Timer

[phendrena]

Hi,

I'm trying to create a userform that displays a countdown timer that will then close when the time is up.

When the user clicks a command button on userform1 to submit data and save the workbook the countdown timer form should appear. While the timer form is displayed all other events should continue as normal in the background (such as the error handler routine that occurs on the error "File locked for saving").

How best can i do this?

Thanks,

[Bob Phillips]

See http://www.xldynamic.com/source/xld.XtraTime.html

[phendrena]

See http://www.xldynamic.com/source/xld.XtraTime.html

Thanks for the reply.
Two things though...

1) The form timer doesn't appear to work with Excel 97,
2) Can't view the code.

Any other suggestions?

Thanks,

[Bob Phillips]

Excel 97? What is the crack with Excel 97, there have been 4 releases since then.

[Bob Phillips]

Here is some code that works in Excel 97.

On the userform., two buttons to start and stop the timer and a label, lblCountdown, and this code

[vba]

Private Sub cmdStart_Click()
nTime = nCount
Call RunTimer
End Sub

Private Sub cmsdStop_Click()
nTime = 0
End Sub
[/vba]

In a general module, this code

[vba]

Public Const nCount As Long = 30 ' secs
Public nTime As Double


Public Sub RunTimer()
If nTime > 1 Then

nTime = nTime - 1
UserForm1.lblCountDown.Caption = Format(TimeSerial(0, 0, nTime), "hh:mm:ss")
Application.OnTime Now + TimeSerial(0, 0, 1), "RunTimer"
Else

Unload UserForm1
End If
End Sub
[/vba]

[phendrena]

Thanks xld, as for '97... blame the company who haven't upgraded in many years. Personally i'm using Office 2002(XP?) at home

[Bob Phillips]

Aah! The pointless release.

You have the worst of all worlds, Excel 97 and XP. If you have to have two, 2000 and 2003 is the best choice IMO, 2000 because it is lean (in MS terms) and will do 99.99% of what anybody needs, and 2003 because it is the last useful, productive release before the mistakes of 2007.

[dkaydkay]

I'm using the code posted above for a countdown timer at the end of a process to give the user a chance to cancel before other macros get called. After the countdown, the rest of the macros proceed if the user doesn't cancel.

The problem I have is that if the user clicks away to another application (to check email or browse the web) while macros are running before the userform is called to open, then the userform doesn't open and the timer doesn't start. Once they click back, the userform pops up and the timer start running.

Any way to make it so that the userform runs when Excel isn't active?

Thanks in advance!

[Bob Phillips]

Struggling to envisage the problem. Can you post your workbook.

[ashleedawg]

Dead Link

[SamT]

10 yo thread. Now a Closed thread.