need a Timer in an Excel Form

[wynnyderpooh]

I need to display a running timer in an Excel form. Start the timer when the
form opens and stop the timer as the form closes.

I use frmTmr with lblTmr
I have attempted to use the Time functions with no success

 

[Bernie Deitrick]

Pooh,

In a regular module, use

Option Explicit
Public StartTime As Date
Public RunTime As Date

Sub ShowmyForm()
Load UserForm1
UserForm1.Show
End Sub

Sub UpdateTime()
RunTime = Now + TimeValue("00:00:01")
UserForm1.TextBox1.Text = Format(Now - StartTime, "hh:mm:ss")
Application.OnTime RunTime, "UpdateTime"
End Sub


On the userform, add a textbox (Textbox1) and use

Private Sub UserForm_Deactivate()
Application.OnTime RunTime, "UpdateTime", , False
End Sub

Private Sub UserForm_Initialize()
StartTime = Now
UserForm1.TextBox1.Text = Format(Now - StartTime, "hh:mm:ss")
RunTime = Now + TimeValue("00:00:01")
Application.OnTime RunTime, "UpdateTime"
End Sub

HTH,
Bernie
MS Excel MVP

 

[wynnyderpooh]

Bernie,
The code is great but it needs a tweak:


in Private Sub UserForm_Initialize()
in get a Compile Error; "Ambiguous name detected".

I have inserted
Option Explict
Public StartTime As Date
Public RunTime As Date in the module and in the form initialize event code.

Can you help?
Thanks,
wynnyderpooh

 

[Chip Pearson]

in get a Compile Error; "Ambiguous name detected".

If you already have a procedure named "UserForm_Initialize", replace
it with Bernie's code or merge his code into yours. You can have only
one procedure named "UserForm_Initialize".

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)