VBA Timer

R

Rana Gray

Board Regular
Joined
Jan 26, 2023
Messages
53
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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

1694791667857.png
1694791692863.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Can you upload a mock workbook to some file sharing website and post a link here ?
 
Upvote 0
It is not clear what each button is supposed to accomplish.

Is the Stop button supposed to reset the timer ? is it supposed to close the form ?
Is the Pause button supposed to close the form ?
What is supposed to happen when closing the userform by clicking the X close button on the titlebar ?

Also, the workbook is huge because you are using too many commandbuttons and userforms. You should be able to reduce the size of the workbook and code since all userforms are running similar code.
 
Upvote 0
Jaafar Tribak said:
It is not clear what each button is supposed to accomplish.

Is the Stop button supposed to reset the timer ? is it supposed to close the form ?
Is the Pause button supposed to close the form ?
What is supposed to happen when closing the userform by clicking the X close button on the titlebar ?

Also, the workbook is huge because you are using too many commandbuttons and userforms. You should be able to reduce the size of the workbook and code since all userforms are running similar code.
Click to expand...
Here is the breakdown:

Start - starts the time
Pause - allows user to pause the time and resume from where it stopped
Stop - will disable all buttons and displays the time when the stop button was clicked and logs it on the sheet under “time worked”
[x] Close - hides the form so it doesn’t erase the data while the workbook is open because if you were to pause and hit the [x ] button it would blank out the user form making the pause button not very useful. The thing is if you leave the user form open and hit pause and continue it works fine but the reality is I’m using this for a team that multitasks so I can’t have user forms open as it prevents anything else from happening .
Unfortunately I need separate forms as they are all for unique tasks and can’t overlap despit having the same function.

Reset - clears the data and allows user to start over (unloads users form) and the click times are logged in the sheet under “resets”

I hope this helps
 
Upvote 0
I am still not quite clear about what each button is supposed to do, particularly the Stop button and whether it is supposed to close the form and remember the last timer.

Anyways, as I said before, the workbook is so unbelievably huge with a crazy number of userforms it is difficult to debug and work with.

I recommend that you use just one userform for all the commandbuttons on the worksheet .. This will reduce the size of the workbook as well as the size of the code enormously and will make the code easier to follow and debug.

The code will dynamically know which button called the userform and will act accordingly.

File demo:
Lender Deal Tracker (REVISED).xlsm


1- This code goes in the ThisWorkbook Module:
VBA Code: 
Option Explicit

Private oCol As Collection

Private Sub Workbook_Open()
    Call HookButtons(Sheet1)  '(TRIAL SHEET)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If oCol Is Nothing Then
        Call HookButtons(Sheet1)
    End If
End Sub

Private Sub HookButtons(ByVal Sh As Worksheet)
    Dim oClass  As CBtnClick, oCtl As OLEObject
    If oCol Is Nothing Then
        Set oCol = New Collection
    End If
    For Each oCtl In Sh.OLEObjects
        If TypeOf oCtl.Object Is CommandButton Then
            Set oClass = New CBtnClick
            Set oClass.Btn = oCtl.Object
            oCol.Add oClass
        End If
    Next oCtl
End Sub


2- This code will be added to a new Class Module ( Class Module name is CBtnClick)
VBA Code: 
Option Explicit

Public WithEvents Btn As CommandButton

Private Sub Btn_Click()
    Call ShowForm(UserForm1, Btn)
End Sub

Private Sub ShowForm(ByVal oForm As Object, ByVal oButn As MSForms.CommandButton)
    oForm.Tag = oButn.Name
    oForm.Show
End Sub


3- This is the code in the UserForm Module:
VBA Code: 
Option Explicit

Private CmdStop As Boolean
Private Paused As Boolean
Private Start As Date
Private TimerValue As Date
Private pausedTime As Date
Private oCurBtn As Object

Private Sub btnStart_Click()
    btnStart.Enabled = False
    btnPause.Enabled = True
    If Len(oCurBtn.AltHTML) Then
        pausedTime = CDate(oCurBtn.AltHTML)
    End If
    Start = Now() - pausedTime
    Do While Paused = False
        TimerValue = Now() - Start
        TimerReadOut = Format(TimerValue, "h:mm:ss")
        DoEvents
    Loop
End Sub

Sub btnPause_Click()
    If btnPause.Caption = "Pause" Then
        Paused = True
        Call UpdateButtonAltHTML
        Unload Me
    Else
        btnStop.Enabled = True
        Call btnStart_Click
    End If
End Sub

Sub BtnReset_Click()
    TimerReadOut = "0:00:00"
    oCurBtn.AltHTML = "0:00:00"
    Unload Me
End Sub

Sub BtnStop_Click()
    btnPause.Enabled = False
    btnReset.Enabled = True
    btnStop.Enabled = False
    CmdStop = True
    With oCurBtn.TopLeftCell
        .Offset(, 9) = TimerReadOut.Value
        If TimerReadOut.Value = "" Then .Offset(, 9) = ""
    End With
    Call UpdateButtonAltHTML
    Unload Me
End Sub

Private Sub BtnReset_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    With oCurBtn.TopLeftCell
        If Shift = 0 Then
            .Offset(, 10) = .Offset(, 10) + 1
        Else
            .Offset(, 10) = .Offset(, 10) - 1
        End If
    End With
End Sub

Private Sub UserForm_Activate()
    Set oCurBtn = Sheet1.OLEObjects(Me.Tag)
    TimerReadOut = oCurBtn.AltHTML
    If Len(oCurBtn.AltHTML) = 0 Or oCurBtn.AltHTML = "0:00:00" Then
        btnStart.Enabled = True
        btnPause.Enabled = False
        btnPause.Caption = "Pause"
        TimerReadOut = ""
    Else
        btnStart.Enabled = False
        btnStop.Enabled = False
        btnPause.Caption = "Continue"
        TimerReadOut = oCurBtn.AltHTML
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Paused = True
    Call UpdateButtonAltHTML
End Sub

Private Sub UpdateButtonAltHTML()
    On Error Resume Next
    oCurBtn.AltHTML = TimerReadOut.Text
End Sub

See how this goes ... It should get you started ... If there are some issues, they should be easier to fix.
 
Last edited:
Upvote 1
Jaafar Tribak said:
I am still not quite clear about what each button is supposed to do, particularly the Stop button and whether it is supposed to close the form and remember the last timer.

Anyways, as I said before, the workbook is so unbelievably huge with a crazy number of userforms it is difficult to debug and work with.

I recommend that you use just one userform for all the commandbuttons on the worksheet .. This will reduce the size of the workbook as well as the size of the code enormously and will make the code easier to follow and debug.

The code will dynamically know which button called the userform and will act accordingly.

File demo:
Lender Deal Tracker (REVISED).xlsm


1- This code goes in the ThisWorkbook Module:
VBA Code: 
Option Explicit

Private oCol As Collection

Private Sub Workbook_Open()
    Call HookButtons(Sheet1)  '(TRIAL SHEET)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If oCol Is Nothing Then
        Call HookButtons(Sheet1)
    End If
End Sub

Private Sub HookButtons(ByVal Sh As Worksheet)
    Dim oClass  As CBtnClick, oCtl As OLEObject
    If oCol Is Nothing Then
        Set oCol = New Collection
    End If
    For Each oCtl In Sh.OLEObjects
        If TypeOf oCtl.Object Is CommandButton Then
            Set oClass = New CBtnClick
            Set oClass.Btn = oCtl.Object
            oCol.Add oClass
        End If
    Next oCtl
End Sub


2- This code will be added to a new Class Module ( Class Module name is CBtnClick)
VBA Code: 
Option Explicit

Public WithEvents Btn As CommandButton

Private Sub Btn_Click()
    Call ShowForm(UserForm1, Btn)
End Sub

Private Sub ShowForm(ByVal oForm As Object, ByVal oButn As MSForms.CommandButton)
    oForm.Tag = oButn.Name
    oForm.Show
End Sub


3- This is the code in the UserForm Module:
VBA Code: 
Option Explicit

Private CmdStop As Boolean
Private Paused As Boolean
Private Start As Date
Private TimerValue As Date
Private pausedTime As Date
Private oCurBtn As Object

Private Sub btnStart_Click()
    btnStart.Enabled = False
    btnPause.Enabled = True
    If Len(oCurBtn.AltHTML) Then
        pausedTime = CDate(oCurBtn.AltHTML)
    End If
    Start = Now() - pausedTime
    Do While Paused = False
        TimerValue = Now() - Start
        TimerReadOut = Format(TimerValue, "h:mm:ss")
        DoEvents
    Loop
End Sub

Sub btnPause_Click()
    If btnPause.Caption = "Pause" Then
        Paused = True
        Call UpdateButtonAltHTML
        Unload Me
    Else
        btnStop.Enabled = True
        Call btnStart_Click
    End If
End Sub

Sub BtnReset_Click()
    TimerReadOut = "0:00:00"
    oCurBtn.AltHTML = "0:00:00"
    Unload Me
End Sub

Sub BtnStop_Click()
    btnPause.Enabled = False
    btnReset.Enabled = True
    btnStop.Enabled = False
    CmdStop = True
    With oCurBtn.TopLeftCell
        .Offset(, 9) = TimerReadOut.Value
        If TimerReadOut.Value = "" Then .Offset(, 9) = ""
    End With
    Call UpdateButtonAltHTML
    Unload Me
End Sub

Private Sub BtnReset_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    With oCurBtn.TopLeftCell
        If Shift = 0 Then
            .Offset(, 10) = .Offset(, 10) + 1
        Else
            .Offset(, 10) = .Offset(, 10) - 1
        End If
    End With
End Sub

Private Sub UserForm_Activate()
    Set oCurBtn = Sheet1.OLEObjects(Me.Tag)
    TimerReadOut = oCurBtn.AltHTML
    If Len(oCurBtn.AltHTML) = 0 Or oCurBtn.AltHTML = "0:00:00" Then
        btnStart.Enabled = True
        btnPause.Enabled = False
        btnPause.Caption = "Pause"
        TimerReadOut = ""
    Else
        btnStart.Enabled = False
        btnStop.Enabled = False
        btnPause.Caption = "Continue"
        TimerReadOut = oCurBtn.AltHTML
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Paused = True
    Call UpdateButtonAltHTML
End Sub

Private Sub UpdateButtonAltHTML()
    On Error Resume Next
    oCurBtn.AltHTML = TimerReadOut.Text
End Sub

See how this goes ... It should get you started ... If there are some issues, they should be easier to fix.
Click to expand...
Thank you so much I’ll try this and let you know!! I appreciate you so much!!
 
Upvote 0
Jaafar Tribak said:
I am still not quite clear about what each button is supposed to do, particularly the Stop button and whether it is supposed to close the form and remember the last timer.

Anyways, as I said before, the workbook is so unbelievably huge with a crazy number of userforms it is difficult to debug and work with.

I recommend that you use just one userform for all the commandbuttons on the worksheet .. This will reduce the size of the workbook as well as the size of the code enormously and will make the code easier to follow and debug.

The code will dynamically know which button called the userform and will act accordingly.

File demo:
Lender Deal Tracker (REVISED).xlsm


1- This code goes in the ThisWorkbook Module:
VBA Code: 
Option Explicit

Private oCol As Collection

Private Sub Workbook_Open()
    Call HookButtons(Sheet1)  '(TRIAL SHEET)
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If oCol Is Nothing Then
        Call HookButtons(Sheet1)
    End If
End Sub

Private Sub HookButtons(ByVal Sh As Worksheet)
    Dim oClass  As CBtnClick, oCtl As OLEObject
    If oCol Is Nothing Then
        Set oCol = New Collection
    End If
    For Each oCtl In Sh.OLEObjects
        If TypeOf oCtl.Object Is CommandButton Then
            Set oClass = New CBtnClick
            Set oClass.Btn = oCtl.Object
            oCol.Add oClass
        End If
    Next oCtl
End Sub


2- This code will be added to a new Class Module ( Class Module name is CBtnClick)
VBA Code: 
Option Explicit

Public WithEvents Btn As CommandButton

Private Sub Btn_Click()
    Call ShowForm(UserForm1, Btn)
End Sub

Private Sub ShowForm(ByVal oForm As Object, ByVal oButn As MSForms.CommandButton)
    oForm.Tag = oButn.Name
    oForm.Show
End Sub


3- This is the code in the UserForm Module:
VBA Code: 
Option Explicit

Private CmdStop As Boolean
Private Paused As Boolean
Private Start As Date
Private TimerValue As Date
Private pausedTime As Date
Private oCurBtn As Object

Private Sub btnStart_Click()
    btnStart.Enabled = False
    btnPause.Enabled = True
    If Len(oCurBtn.AltHTML) Then
        pausedTime = CDate(oCurBtn.AltHTML)
    End If
    Start = Now() - pausedTime
    Do While Paused = False
        TimerValue = Now() - Start
        TimerReadOut = Format(TimerValue, "h:mm:ss")
        DoEvents
    Loop
End Sub

Sub btnPause_Click()
    If btnPause.Caption = "Pause" Then
        Paused = True
        Call UpdateButtonAltHTML
        Unload Me
    Else
        btnStop.Enabled = True
        Call btnStart_Click
    End If
End Sub

Sub BtnReset_Click()
    TimerReadOut = "0:00:00"
    oCurBtn.AltHTML = "0:00:00"
    Unload Me
End Sub

Sub BtnStop_Click()
    btnPause.Enabled = False
    btnReset.Enabled = True
    btnStop.Enabled = False
    CmdStop = True
    With oCurBtn.TopLeftCell
        .Offset(, 9) = TimerReadOut.Value
        If TimerReadOut.Value = "" Then .Offset(, 9) = ""
    End With
    Call UpdateButtonAltHTML
    Unload Me
End Sub

Private Sub BtnReset_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
    With oCurBtn.TopLeftCell
        If Shift = 0 Then
            .Offset(, 10) = .Offset(, 10) + 1
        Else
            .Offset(, 10) = .Offset(, 10) - 1
        End If
    End With
End Sub

Private Sub UserForm_Activate()
    Set oCurBtn = Sheet1.OLEObjects(Me.Tag)
    TimerReadOut = oCurBtn.AltHTML
    If Len(oCurBtn.AltHTML) = 0 Or oCurBtn.AltHTML = "0:00:00" Then
        btnStart.Enabled = True
        btnPause.Enabled = False
        btnPause.Caption = "Pause"
        TimerReadOut = ""
    Else
        btnStart.Enabled = False
        btnStop.Enabled = False
        btnPause.Caption = "Continue"
        TimerReadOut = oCurBtn.AltHTML
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    Paused = True
    Call UpdateButtonAltHTML
End Sub

Private Sub UpdateButtonAltHTML()
    On Error Resume Next
    oCurBtn.AltHTML = TimerReadOut.Text
End Sub

See how this goes ... It should get you started ... If there are some issues, they should be easier to fix.
Click to expand...
I just had an opportunity to test everything and I must say no amount of thanks is enough, I was hoping you might be able to help me tweak something if it isn't too much trouble. For the stop button I need to capture the total amount of time worked by the lender for that deal, meaning I don't want them to be able to manipulate (or add time) after the fact - so when the stop button is clicked the only button that should be enabled is the "Reset" button. The second issue I'm struggling with is saving the "pause" time specifically so if a deal carries over to the following day they can resume the timer from where they left off.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA - Timer Functions
Replies
0
Views
589
Rana Gray
R
V
  • Solved
Workbook with Timer Macro Saving After Closing
Replies
1
Views
442
John_w
J
E
  • Question
Auto close spreadsheet timer not working correctly
2
Replies
17
Views
779
eli_m
E
R
  • Question
Three-lane Stopwatch?
Replies
7
Views
169
RaymondW
R
MsDeth
  • Question
Limit timer-running macro to one workbook, regardless of how many workbooks are open
Replies
6
Views
514
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,215,076
Messages
6,122,984
Members
449,092
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top