Solved: Time and Date log using VBA

[Mitchelson]

Hi,

I want to set-up a time and date log using VBA. I need the time to start when the spreadsheet is opened and the time to stop when the spreadsheet is closed. I would like the log to be recorded as show below in the same workbook on a hiden sheet.

Time opened -----Time closed
12/04/08 08:00 ---12/04/08 17:00
13/04/08 08:00 ---13/04/08 12:00

I'm new to VBA and any help will be greatly appreciated

[Simon Lloyd]

Put this in the ThisWorkbook module (Alt+F11, double click Thisworkbook on left hand side)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Application.ScreenUpdating=False
Sheets("Hidden").visible=True
Set Rng = Sheets("Hidden").Range("B" & Rows.Count).End(xlUp)
Rng.Offset(0, 1).Value = Now()
Sheets("Hidden").Visible=xlVeryHidden
Application.ScreenUpdating=True
End Sub
Private Sub Workbook_Open()
Dim Rng As Range
Application.ScreenUpdating=False
Sheets("Hidden").visible=True
Set Rng = Sheets("Hidden").Range("A" & Rows.Count).End(xlUp)
Rng.Value = Environ("username")
Rng.Offset(0, 1).Value = Now()
Sheets("Hidden").Visible=xlVeryHidden
Application.ScreenUpdating=True
End Sub

[Mitchelson]

The code works great, thank you very much. All I done is remove the auto hide part of the code for now so I can view the updates.

Private Sub Workbook_Open()
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp)
Rng.Value = Environ("username")
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp)
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub


At the moment it updates open and close time on single row as shown below.

Name------------------Open------------------------Close
F046915 -----07/05/2008 15:21 ------07/05/2008 15:21

I would like to add each new open and close time entry on the new row. So I will be able see how much time the user as worked on the workbook over a week or month.

[Bob Phillips]

Untested, but try this small mod to Simon's code



Private Sub Workbook_Open()
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1,0)
Rng.Value = Environ("username")
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp)
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub

[Simon Lloyd]

Mitchelson i would have thought it would be better to see each open and close on a single line!, perhaps adding a formula to the cell adjacent to the close time for working out exact hours and minutes, entries one under the other for each open and close will get confusing when the workbook has been opened many times.

[Mitchelson]

Thank you both foryour help. The code works well. I added "ThisWorkbook.save" because if you did not save when you were closing down the workbook you lost the close time for that session.


Private Sub Workbook_Open()
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.Value = Environ("username")
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Rng As Range
Application.ScreenUpdating = False
Set Rng = Sheets("sheet2").Range("B" & Rows.Count).End(xlUp)
Rng.Offset(0, 1).Value = Now()
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub

[IgnBan]

?
If this was a workbook in a network drive where two people can open it at the same time and will open "Read Only" for one of them..would it still record both users opening and closing the workbook?

[Simon Lloyd]

ignban it should work fine but good point!

[mdmackillop]

I would be very wary about forcing a save. I've often closed without saving to recover from some undesirable/unwanted changes I've made.

[IgnBan]

Thank you Simmon
Saved in my toolbox!

[Bob Phillips]

?
If this was a workbook in a network drive where two people can open it at the same time and will open "Read Only" for one of them..would it still record both users opening and closing the workbook?

No, because the Save would fail on the read-only version.

[Simon Lloyd]

Bobs right, it was a quick glib return post of mine as i was on my way out!! apologies.

[Bob Phillips]

But it would work if you logged it to a separate file, such as text file.

[Simon Lloyd]

Hmmmm lateral thinking Bob!, thats why you will forever be the master!....although it does present its own difficulty with having to do the time calculation of the fly or presenting it in a cell prior to passing it to the text document (i believe!).

[Bob Phillips]

Not sure that I understand you Simon, but VBA can format the NOW method as a time string.

[Simon Lloyd]

Because the Op wanted to calculate the amount of time a user had been on so the calculation for that has to occurr in Excel either in VBA or in a cell.

[Bob Phillips]

Oh right! Yes, the calculation would be in VBA on the close, but as you know that is not difficult.

It also adheres to isolating the code from the data, this is data, but not business data, and it is not code.

[Aussiebear]

Can we also log the user? This way you'll know who was on for how long?

[Bob Phillips]

I think Simon's code was already doing that.

[Simon Lloyd]

Ted, its achieved here: Rng.Value = Environ("username") This is the network username if you want the application username then its just that Application.UserName

[Aussiebear]

Sorry.... I should have read your code more slowly.:doh:

[mdmackillop]

Not your fault Ted, he types too quickly! :type