How to Keep Running Stopwatch running when using other sheets and other workbooks

Hi All,
I am new to VBA and most of what I know comes from the internet. I have created a worksheet that will allow our team to track how much time they are spending on certain tasks by use of active stopwatch timers. The timers keep cumulative time by what I am sure is a really primitive method. Each time the timer stops it adds the time to another sheet that then feeds back into the active sheet to keep the total time worked. However, the timers are stopping when I try to do other work. I need the timers to continue even when someone is working on other sheets and workbooks (with macros). The intention is for someone to open this and start the timer and then go about their work. I attached the file.

Oops. Sorry about that. The passwords are all “contracts”

Hi again, I know everyone is super busy, but if anyone could help me that would be amazing. This tracker is super important for me and if I can get it to work it be great!

Hi #9,

There are several ways to create timers to do what you want. I have tried 5 that I know of and they all fail in Office 365 (cause Excel to hang up or crash) when you try to enter data into a cell.

Having said that, I was successful by manually (CommandButton) turning off the timer while entering data into a cell. Then I used another CommandButton to turn the timer back on. The Elapsed time was correct.

A second method is to have the Workbook in a separate instance of Excel. See https://excelgorilla.com/excel/gener...stances-excel/

Lewis

I was successful in doing what you want. I will post the solution in the next reply.

In this reply I will post code for five different timing methods.

See the attached file for five different timing methods implemented on a Spreadsheet and also in a UserForm. Each method has pros and cons listed in the file.
a. Software loop
b. API Sleep() function
c. Application.Wait
d. Application.OnTime
e. API SetTimer function

NOTE: The SetTimer method will cause Excel to crash if VBA code is modifying cells at the same time the User is updating the Spreadsheet.
The use of formulas to implement API SetTimer (courtesy of Jaafar Tribak) is a workaround to this problem
See May 27, 2009 post #3 in https://www.mrexcel.com/board/thread...t-cell.392397/
Thank you Jaafar

The code below is for Spreadsheet implementations. For UserForm implementation see the attached file.
In an ordinary code module (e.g. ModOneSecondDelayExamples):

Please Login or Register  to view this content.

Lewis

I was successful in modifying your spreadsheet to allow Modifying the spreadsheet (including UNDO) without stopping the timers and without crashing Excel.
The use of formulas to implement API Timer is courtesy of Jaafar Tribak
See May 27, 2009 post #3 in https://www.mrexcel.com/board/thread...t-cell.392397/
Thank you Jaafar
Please note that 'real time' code is difficult to debug, so there may still exist some unwanted 'side effects'. Please let me know if you have any problems.

Notes:
a. Implemented two timers only - Client Tasks 1 and 2.
Multiple Timers can run simultaneously or there is a limit of one 'Active Timer' only - chosen by a PERMANENT VBA change. See Sub LogicallyStopAllTimers() for instructions.
Whether one or multiple Logical Timers, there is only one SetTimer() Timer running.
b. Sub AdjustValue() in code module ModOriginal was changed to xAdjustValue() to avoid a conflict with a modified routine in code module ModNew.
c. The remainder of Sheet2 Code module VBA was NOT changed - Reset should work exactly as before.
d. Client Timer1 and Timer2 code was commented out in Code Module ModOriginal.
e. When Deleting Sheets Runtime Error Occurs (50290 = Application-defined or object-defined error) but is trapped by the software.

How the software works:
a. One API Timer is used using API code SetTimer() and KillTimer().
SetTimer() creates a timer that repeats at a specified interval until stopper by KillTimer().
b. Your data design was left intact as much as possible. VBA code affects Sheet 'Data_tab' as follows using 'Client Timer 1' as an example:
(1) Cell X3 = No change
(2) Column W = No change
(3) Cell V3 contains the formula '=NOW() - Data_tab!U3' when Timer1 is active
(3) Cell V3 = the VALUE that was in cell V3 when STOP was Clicked
(4) CellU3 = the Baseline (when Start was Clicked) Date and Time when Timer1 is active
(4) CellU3 = BLANK when Timer1 is NOT active
c. Sub APITimerEventHandler() runs each wake cycle of the generic timer (approximately at 1 second intervals)
APITimerEventHandler() causes 'Calculate' to occur for each active timer (e.g. Cell V3 for Client Timer1.
The use of 'Calculate' instead of writing to the spreadsheet directly allows Spreadsheet changes including Adding Sheets and UNDO while the Timer is running.
It is recommended that Suspend/Resume be used when Deleting Sheets.
d. Modifying VBA while the Timer is active can cause Excel to crash. The use of the 'Suspend' and 'Resume' CommandButtons can stop this from happening.
e. Occassionally Runtime Error '50290 Application-defined or object-defined error' occurs. 'On Error Resume Next' (i.e. ignoring the error) seems to mitigate the problem.

Important code excerpts follow. See the attached file for complete code:

Please Login or Register  to view this content.

Additional remarks and suggestions:
a. File with passwords take a lot of extra time to debug. Do not implement passwords until debugging is completed.
b. To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). https://www.excel-easy.com/vba/examp...-explicit.html

Lewis

Due to space limitations some code is continued in the next reply.

Additional code in the same code module as in the previous reply:

Please Login or Register  to view this content.

Lewis