Excel VBA - Display elapsed time since start on userform

[Rodopi]

Using VBA, I am trying to display Elapsed Time since start of application on a label contained on a userform.

I am using Excel 2007.

I have never used the date/time and timer functions before,and after exhausted searches on the internet I still haven't found a solution and I am even more confused

If anyone can point me in the right direction I would really appreciate it.

Regards
Rodopi

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rodopi]

Thanks for responding PHV.

I gave up on the idea of having an automatic display of the elapsed time since start......just found it too confusing.

I thought better walk before attempting to run so I have now implemented a successful halfway solution that updates the elapsed time display every time the user enters data.

Not particularly elegant but will have to suffice until I can improve my knowledge in this area.

For anyone that is interested in my solution the code is very simple and is as follows:

Module1:

Public StartTime As Single
-------------------------------
Sub Open_Dashboard_Click()

StartTime = Timer

MyFormfrm.Show

End Sub

When user enters data a procedure is called and within that I update the elapsedtime label as follows:

' Calculate Elapsed Time so far and display on dashboard

elapsedtime = Timer - StartTime

ETimelbl.Caption = Format(CDate(elapsedtime / 86400), "hh:nn:ss")

That's it....works a treat.

PHV, if you can give me any pointers on how to implement a dynamic and continuous display of elapsed time I would really appreciate it.

Thanks once again for responding.

Best Regards
Rodopi

 

[PHV]

how to implement a dynamic and continuous display of elapsed time
Module1:

Public StartTime As Single
Sub Open_Dashboard_Click()
StartTime = Timer
MyFormfrm.Show
End Sub
Public Sub myTimer()
Dim elapsedtime As Single
elapsedtime = Timer - StartTime
MyFormfrm.ETimelbl.Caption = Format(CDate(elapsedtime / 86400), "hh:nn:ss")
Application.OnTime Now + TimeValue("00:00:01"), "myTimer"
End Sub

In the form's module:

Private Sub UserForm_Initialize()
Call myTimer
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, forgot to cancel the timer.
Module1:

Public StartTime As Single
Sub Open_Dashboard_Click()
StartTime = Timer
MyFormfrm.Show
[!]Application.OnTime Now + TimeValue("00:00:01"), "myTimer", , False[/!]
End Sub
Public Sub myTimer()
Dim elapsedtime As Single
elapsedtime = Timer - StartTime
MyFormfrm.ETimelbl.Caption = Format(CDate(elapsedtime / 86400), "hh:nn:ss")
Application.OnTime Now + TimeValue("00:00:01"), "myTimer"
End Sub

Note: the UserForm should be modal

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886