Logging time in Excel

[Guest]

I have a large model that is a template for negotiation events. A lot of
people use this template. I would like to build in a macro that tracks the
amount of time each user spends in the workbook. I'm thinking they would
click a button that would start the timer and the timer would run as long as
they had the workbook open. The timer would stop when they close the
workbook, but restart whenever the workbook is opened again. We need this
information for reporting on the amount of time spent working in negotiations.

 

[Bob Phillips]

Create a worksheet and name it Log, then hide that worksheet

Option Explicit

Private nTime As Double

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iFreeRow As Long
With Worksheets("Log")
iFreeRow = .Cells(.Rows.Count, "A").End(xlUp).Row
If iFreeRow > 1 Or .Range("A1").Value <> "" Then
.Range("A1").Value = "User"
.Range("B1").Value = "Logged In"
.Range("C1").Value = "Logged Out"
.Range("D1").Value = "Elapsed"
End If
iFreeRow = iFreeRow + 1
.Cells(iFreeRow, "A").Value = Environ("UserName")
With .Cells(iFreeRow, "B")
.Value = nTime
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
With .Cells(iFreeRow, "C")
.Value = Now
.NumberFormat = "dd mmm yyyy hh:mm:ss"
End With
With .Cells(iFreeRow, "D")
.Value = Now - nTime
.NumberFormat = "hh:mm:ss"
End With
End With
thosworkbook.Save

End Sub

Private Sub Workbook_Open()
nTime = Now
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

It would be a little more fool-proof if the user did not have to puch a
button. Just have the macro record
=NOW()
whenever the workbook is opened or closed.