Multiple timer for running a workshop.

[arniej]

Hi All

I am running a spreadsheet to keep track of workshop technician times. The code I am using I have copied and modified from this forum works really well but I need the timer to go in minus time instead of stopping at 00:00:00. It work be really great if the timer cell changed colour it identify that the Technician had run over the allotted time.

Many thanks.

Here is the code that i am currently running.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Please Login or Register  to view this content.

[LJMetzger]

Hi arniej and welcome to Excel Forum,

Please read the Forum Rules. We are not allowed to answer your question because you didn't add CODE TAGS to your code. Because it is your first post, I will answer, but no further responses will be allowed (or the responder will get in trouble) until you add Code Tags. See http://www.excelforum.com/forum-rule...rum-rules.html

Very interesting problem. For some unknown reason 'Negative Time' doesn't exist in the Excel 'Space/Time Continuum. I had to fool Excel by posting negative time as a string, then converting the string back to time as required. Please NOTE, that I put in some CONDITIONAL COMPILATION code that stops the negative time at -10 seconds. Take it out for the timer to run forever or changes it to meet your requirements. I was able to stop the runway timer using CTRL-Break.

Try the following code (tested using Excel 2003):

Please Login or Register  to view this content.

Lewis

[arniej]

Sorry but this code will not work. The timer will not start at all.

[LJMetzger]

Hi,

Thanks for your patience, and thanks for posting the workbook. It looks like an excellent design.

1. When I made the initial changes, I thought the timers would be in order. Now that I see your worksheet, I corrected my error, which caused the worksheet not to work for you. It had to do with the line:

Please Login or Register  to view this content.

because I didn't realize all the rows had to be scanned, for each pass.

2. I suggest you change the formatting of Column C so it displays hours, minutes, and seconds, as follows:

Please Login or Register  to view this content.

3. The 'wait' you used for each loop hogs the computer CPU. I tried to give you more control of the workbook by rescheduling 'AdjustTimer()' to run once per second until there were no more active timers by replacing:

Please Login or Register  to view this content.

4. I am attaching a workbook written in Excel 2003, with all the changes. My Excel 2003 file did not have access to your Active X controls, so I pasted a couple of shapes to do 'START' and 'CLEAR'. If you use your workbook, you should be fine if you:
a. Change the formatting of Column C.
b. Use the new 'AdjustTimer().

Lewis

Code follows (new changes in red):

Please Login or Register  to view this content.

Lewis

Attachment was deleted due to code errors. See the file associated with post #8 for the updated attachment.

[arniej]

Hi again,

I am sorry but I am now totally lost.

I do not know where to add the additional code:

Option Explicit

Public wSht As Worksheet

Sub StartTimer()

Set wSht = ActiveSheet
Call AdjustTimer

End Sub

Also you mentioned that you had added a working file. I cannot find the file.

Many thanks again

[LJMetzger]

Please forgive the confusion. I'm usually a lot better than that. I edited previous posts because you were not on-line at the time, and I probably shouldn't have. Post #4 should have all the information you need.

If you have any more problems or questions, please ask.

Lewis

[arniej]

Many many thanks again Lewis.

The only issue is that the timer counts are relevant to the number of Tech's working. ie If there are five technicians all being timed at the same time the clock counts down in 5 second increments..

Any Idea's

[LJMetzger]

Hi,

This is just one of those projects where my approach always seems to be slightly off. The problem was caused by the timer being run multiple times in parallel, each time you activated a new user.

The fix was to deactivate the timer, and start it up right away, each time you press 'Start' from the UserForm. I had to make changes in the code in 3 places:
a. ThisWorkbook module - Added Sub Workbook_BeforeClose() to turn off the timer (if it is running) when you close the workbook.
b. TimerMod module - Sub AdjustTimer () - The code to reschedule the timer was in the wrong place. It should have been outside of the loop. I had to make the time to reschedule Sub AdjustTimer() global, in order to stop the timer from other modules.
c. UserForm TimerUsf Module - Sub Start_Click() - Added code to Stop the timer before calling Sub AdjustTimer().

Try the attached file. Please let me know if there are any more problems. When you post something to this thread, I automatically get an e-mail.

Lewis

Code affected. Changes in Red.

ThisWorkbook module:

Please Login or Register  to view this content.

TimerMod Module:

Please Login or Register  to view this content.

UserForm TimerUsf Module:

Please Login or Register  to view this content.

[arniej]

Hi Lewis

Many thanks again, This is exactly what I wanted.

I could never have done this without your help.

Thank you so much