Time Excel worksheet is open?

Posted by Dawny on August 22, 2001 12:14 AM

Has anyone written code for adding up the total time a workbook has been opened?

i.e user opens workbook, cell A1 on worksheet "time" registers this time, wokbook is closed cell A2 register the end time, a macro then subtracts A1 from A2 and adds the time to another sheet "log|" which shows the date and time open for each event.

Thanks
Dawny

Posted by Rodney on August 22, 2001 4:56 AM

Try this :-

Private Sub Workbook_Open()
Worksheets("Time").Range("A1").Value = Now
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Time As Worksheet, Log As Worksheet
Set Time = Worksheets("Time")
Set Log = Worksheets("Log")
Time.Range("A2").Value = Now
With Log.Range("A65536").End(xlUp).Offset(1, 0)
.Value = Time.Range("A1").Value
.Offset(0, 1).Value = Time.Range("A2").Value
With .Offset(0, 2)
.Value = Time.Range("A2").Value - Time.Range("A1").Value
.NumberFormat = "[h]:mm:ss"
End With
End With
End Sub