Excel VBA Keep Userform Timer running when Userform or Excel are closed

Question

Recently I've managed to find some code regarding a timer on a userform, my problem is that I need to keep the timer running even if the userform or excel file is closed... can someone take a look at the code and provide some feedback? My userform is: optionsForm

Dim dteStart As Date, dteFinish As Date  
Dim dteStopped As Date, dteElapsed As Date  
Dim boolStopPressed As Boolean, boolResetPressed As Boolean

Private Sub Reset_Timer_Click()  
dteStopped = 0  
dteStart = 0  
dteElapsed = 0  
Tech_Timer = "00:00:00"  
boolResetPressed = True  
End Sub  

Private Sub Start_Timer_Click()  
Start_Timer:  
dteStart = Time  
boolStopPressed = False  
boolResetPressed = False  
    Timer_Loop:  
           DoEvents  
             dteFinish = Time  
             dteElapsed = dteFinish - dteStart + dteStopped  
    If Not boolStopPressed = True Then  
        Tech_Timer = dteElapsed  
    If boolResetPressed = True Then GoTo Start_Timer  
    GoTo Timer_Loop  
Else  
    Exit Sub  
End If  
End Sub  

Private Sub Stop_Timer_Click()  
boolStopPressed = True  
dteStopped = dteElapsed  
End Sub  

Private Sub optionsForm_Initialize()  
Tech_Timer = "00:00:00"  
End Sub

Just an idea - if the excel file is closed, save the value of the form in a cell of excel and then continue from it when it is opened. Would it work like this? — Vityata
– Vityata, Commented Mar 3, 2017 at 9:19
Ye, no problem with that.... I can create and hide a new tab, sheet1 for that... — EdN
– EdN, Commented Mar 3, 2017 at 9:21

Vityata · Accepted Answer · 2017-03-03 09:43:07Z

2

The idea of the timer is not that it runs, but that it remembers a point in time and can give you a difference between this point and the current moment. If you ask for this difference every second, then it would look like it is running like a watch.

Something like this would be a good start. In the xl_main write the following:

Option Explicit

Dim dtime As Date

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Cells(1, 1).Value = dtime
End Sub

Private Sub Workbook_Open()

    If Cells(1, 1).Value = 0 Then
        dtime = Now
    Else
        dtime = CDate(Cells(1, 1))
    End If

End Sub

You may play around it and make it better as you wish. E.g. you may find a way to reset dtime or anything similar.

edited Mar 3, 2017 at 9:43

answered Mar 3, 2017 at 9:22

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EdN Over a year ago

Thks for the comment and that important piece of code, but how do i post it in the userform? Can I do something like: Tech_Timer = dtime? , I mean, directly in that label in the userform?

Vityata Over a year ago

More like Tech_Timer = cdate(now-dtime)

EdN Over a year ago

ok, I figured what to change :) however need to think a bit more over this because I have several userforms and each of them needs to take individual timers. this way, they all get the same timer

ashleedawg · Accepted Answer · 2018-06-06 16:16:03Z

"Something" needs to be running to handle the timer procedure so if you want to use VBA then Excel can't be "closed" per se, however you could make it appear closed.

An obvious option is to minimize the Excel window (before showing the userform) with the WindowState property:

Application.WindowState = xlMinimized

...or, hide the Excel window completely with the Visible property:

Application.Visible = False

...or if the issue is that you need a "fresh" copy of Excel to work in, you could do so in a new instance by holding Alt while starting Excel.

I have posted code and a downloadable example of a countdown timer that displays the time remaining on a userform semi-independent of the Excel window, using the Windows Timer API (instead of Excel's procedure), in another answer here.
timer

Pᴇʜ · Accepted Answer · 2017-03-03 09:17:01Z

0

That's not possible if the form is unloaded Unload optionsForm. But you can try to 'close' the form with optionsForm.hide() this only hides the form, the timer should keep running then.

The only way I see to calculate the time passed from a start time even if Excel is closed is to not save the start time in a variable dteStart but in an Excel cell.

answered Mar 3, 2017 at 9:17

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

2 Comments

EdN Over a year ago

Hi Peh, yes, I currently have a Close button for the userform that unloads it .... will test the hide... will it keep running if I close the excel file? .... right, doesnt work...

Pᴇʜ Over a year ago

No! therefore you will need to use a Excel cell to save the start time.

Henqsoft solutions · Accepted Answer · 2020-12-04 22:01:07Z

0

Actually you can use a code that is placed in a module. The code is:

Option Explicit
Dim T

Sub stopTimer()
On Error Resume Next
Application.OnTime (T), Procedure:="Update", Schedule:=False
End Sub

Sub StartTimer()
T = Now + TimeValue("00:00:01")
Application.OnTime T, "Update"
End Sub

Sub Update()
UserForm1.TextBox1.Value = Format(Now - Sheets("Sheet1").Range("E11").Value, 
"hh:mm:ss")
UserForm1.TextBox2.Value = Format(TimeValue("1:00:00") - (Now - 
Sheets("Sheet1").Range("E11").Value), "hh:mm:ss")
Call StartTimer
End Sub

Thereafter, you can now reference it in the userform by calling it. Here is a typical example. It is

Private Sub Userform_Activate()
Sheet1.Activate
Sheets("Sheet1").Range("E11").Value = Now
Application.Run "StartTimer"
If Sheets("Sheet1").Range("K27").Value = "K29" Then
Me.CommandButton4.Caption = "Start"
Me.CommandButton2.Visible = False
End If
End Sub

answered Dec 4, 2020 at 22:01

Henqsoft solutions

1

1 Comment

Henqsoft solutions Over a year ago

This worked very fine when we used it as a timer for my CBT which we just created.

Collectives™ on Stack Overflow

Excel VBA Keep Userform Timer running when Userform or Excel are closed

4 Answers 4

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related