date/time & timer

[paveitcans]

Hello, I'm looking for help with a worksheet that will in cell k17 automatically give the current date/time once "clicked" or activated in some way. Then what I'd like cell k19 to do is to start a timer that will show how long it has been since k17 was activated in dd/hh/mm and will then be stopped or frozen once another cell or utility is checked to confirm that the work order has been completed. Any help at all with this would be greatly appreciated and putting it in laymans terms would be even greater.

Thanks in advance.

 

[sbendbuckeye]

Are you comfortable doing a little bit of VBA code behind your file?

 

[paveitcans]

I'm sort of learning as I go with great interest. I'll certainly try

 

[paveitcans]

Willing to give it a shot. Have minimal self taught vb skills.

 

[sbendbuckeye]

Here is what I think you are saying:

1) Put current datestamp in K17 when some action is triggered - how is the action defined and how will Excel know when it has occurred (eg A7 is changed, etc)

2) Start a time to show elapsed time since K17 was updated - how often does it need to be updated - should it be changing the cell display while sitting there doing nothing, etc

3) Stop the timer and freeze K19 when the process is terminated - how is it defined and how do I reconize it is complete

If the above is correct, please post a couple examples which demonstrate how you would like it to work. Good Luck!

 

[paveitcans]

1. I've got the timestamp updating with a command button. the timestamp has been moved to cell m37 and the code looks like this:

Private Sub CommandButton1_Click()
Range("m37").Value = Now
Range("m37").NumberFormat = "mm/dd/yyyy hh:mm:ss"

End Sub

2. I'd like there to be a running clock to show the elapsed time.

3. to stop the timer: there will be another command button to check by the supervisor once the task is complete. I cant figure out how to paste a copy of the worksheet to demonstrate &*$%#@*:

 

[sbendbuckeye]

Create a user form named frmHidden and drop this code in it:

Option Explicit

Public gContinue As Boolean
Public gCurrentTimer As Single
Public gDisplayAddress As String 'eg C10

Private Sub UserForm_Initialize()
    gContinue = True
    gCurrentTimer = Timer
    Call CalcTime
End Sub 'UserForm_Initialize

Private Sub CalcTime()
Dim lngTimer As Long
    lngTimer = Int(gCurrentTimer)
    Do While gContinue
        'Loop for a second
        Do While Int(Timer) - lngTimer < 1
            'Allow system events, other keys and clicks to be processed
            DoEvents
        Loop
        'Display new time
        Call DisplayTime(Timer)
        'Reset and start the process over
        lngTimer = Int(Timer)
    Loop
    'Finished so unload form
    Unload Me
End Sub 'CalcTime

'Assumes gDisplayAddress has been set by calling code
Private Sub DisplayTime(CurTimer As Single)
Dim sngTemp As Single
Dim strTemp As String
Dim blnCheckHours As Boolean, blnCheckMinutes As Boolean
Dim lngHours As Long, lngMinutes As Long, lngSeconds As Long
    If gCurrentTimer = 0 Then gCurrentTimer = Timer
    sngTemp = CurTimer - gCurrentTimer
    blnCheckMinutes = True: blnCheckHours = True
    Do While sngTemp > 0
        If lngHours = 0 And blnCheckHours Then
            blnCheckHours = False
            lngHours = sngTemp \ 3600
            sngTemp = sngTemp - (lngHours * 3600)
        End If
        If lngMinutes = 0 And blnCheckMinutes Then
            blnCheckMinutes = False
            lngMinutes = sngTemp \ 60
            sngTemp = sngTemp - (lngMinutes * 60)
        End If
        If lngSeconds = 0 Then
            lngSeconds = Int(sngTemp)
        End If
        strTemp = Format(lngHours, " 00:") & Format(lngMinutes, "00:") & Format(lngSeconds, "00")
        Do While Left$(strTemp, 1) = " "
            strTemp = Replace$(strTemp, " 00:", " ")
            strTemp = Replace$(strTemp, " 0", vbNullString)
        Loop
        Exit Do
    Loop
    'Allow calling code to set DisplayAddress after form is loaded
    If Len(Trim$(gDisplayAddress)) > 0 Then
        ThisWorkbook.ActiveSheet.Range(gDisplayAddress) = strTemp
    End If
End Sub 'DisplayTime

Then modify your button click code slightly as below:

Private Sub CommandButton1_Click() 
    Range("m37").Value = Now 
    Range("m37").NumberFormat = "mm/dd/yyyy hh:mm:ss" 
    Load frmHidden 'Since you did not use Show it is hidden
    gDisplayAddress = "A1" 'or whatever
End Sub 

Private Sub CommandButton2_Click() 
    gContinue = False
End Sub

Using a hidden form allows code to be running asyncronously with your looping code. The global variables in the form mean you can call them directly. It is considered better coding technique to use public properties but that would complicate the code a bit and obscure your questions. Good Luck!