VBA Timer

[Rana Gray]

Hey Everyone!

So I am getting a bit stuck here - I have a timer that works (for the most part) the issue is when the userform is hidden and re-opened the "Continue" feature doesn't work anymore. Meaning the timer doesn't continue counting. Hope I can get some help please see my code below and a screen grab of the user form for reference. (Side note if I keep the UserForm open and click "continue" it works fine - hiding it somehow disrupts the code).

Option Explicit
Dim CmdStop As Boolean
Dim Paused As Boolean
Dim Start
Dim TimerValue As Date
Dim pausedTime As Date

Sub btnStart_Click()

btnPause.Enabled = True

CmdStop = False
Paused = False
Start = Now() ' Set start time.
btnPause.Enabled = True
btnStop.Enabled = True
btnReset.Enabled = False
Do While CmdStop = False
If Not Paused Then
TimerValue = Now() - Start - pausedTime
Else
pausedTime = Now() - TimerValue - Start
End If
TimerReadOut = Format(TimerValue, "h:mm:ss")
DoEvents ' Yield to other processes.
Loop
End Sub

Sub btnPause_Click()

btnStart.Enabled = False

If btnPause.Caption = "Pause" Then
Paused = True
btnPause.Caption = "Continue"
Else
Paused = False
btnPause.Caption = "Pause"
End If

End Sub
Sub BtnReset_Click()
TimerReadOut = "0:00:00"
btnStop.Enabled = False

Unload UserForm1
End Sub
Sub BtnStop_Click()
btnPause.Enabled = False
btnReset.Enabled = True
btnStop.Enabled = False
CmdStop = True

Sheet1.Range("M8").Value = TimerReadOut.Value
If TimerReadOut.Value = "" Then Sheet1.Range("M8").Value = ""
UserForm1.Hide

End Sub

Private Sub BtnReset_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Shift = 0 Then
Range("N8") = Range("N8") + 1
Else: Range("N8") = Range("N8") - 1
End If
End Sub

Private Sub UserForm1_Initialize()

TimerReadOut.Value = Sheet14.Range("C3").Value
Sheet14.Range("C3").Value = TimerReadOut.Value
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If CloseMode = vbFormControlMenu Then

Me.Hide

Cancel = True

End If
End Sub

 

[Jaafar Tribak]

Not sure if I fully understood but see if this update achieves what you want :

File Demo:
Lender Deal Tracker (REVISED 2).xlsm

Note: I am now using Column A:A ( starting from cell A8) as a helping column which is expressed in the code as oCurBtn.TopLeftCell.Offset(, -3) that is 3 columns to the left of each commandbutton.

This helper column will be used to store the Stopped\Reset status of the timer.

You can use some other column other than column A:A but if you do that, you will need to adjust the offset in the code.

If using column A:A is fine but you don't want the values to be seen by the user, you can format the column by carying out the following steps:
Select the column, Righ click, choose Format Cells > Number > Custom > Type : ;;;

You could also lock the column to avoid accidental editing.

 

[Rana Gray]

Jaafar I don't know what I would do without you!! Thank you so much! It's perfect!! I wish you taught classes haha