Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logging time in Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logging time in Excel
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) "Chad81" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Logging time in Excel
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. -- Gary''s Student gsnu200708 "Chad81" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
logging out | Excel Programming | |||
Logging username, date and time | Excel Programming | |||
Where might I find an Excel template for logging DVD movies | Excel Discussion (Misc queries) | |||
DDE Logging Data Loss in Excel | Excel Discussion (Misc queries) | |||
logging excel events? | Excel Programming |