CODEWITHSUNDEEP
vbaexcel
EdN
EdN

Reputation: 41

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

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

Upvotes: 2

Views: 2611

Answers (4)

Henqsoft solutions
Henqsoft solutions

Reputation: 1

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

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21619

"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.
            new instance

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

Upvotes: 1

Vityata
Vityata

Reputation: 43575

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.

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

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.

Upvotes: 0

Related Questions