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

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


"Bernie Deitrick" wrote:

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

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)



On Wed, 11 Mar 2009 11:16:05 -0700, wynnyderpooh
wrote:

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


"Bernie Deitrick" wrote:

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

Yeah...what Chip said. (Of course!)

And you should only declare your variables once, at the top of your regular
codemodule.

HTH,
Bernie
MS Excel MVP

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


"Bernie Deitrick" wrote:

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