PDA

View Full Version : Recording time spent on a worksheet



samohtwerdna
11-18-2005, 08:50 AM
OK, another simple question for the masters:thumb

I want to record the time spent on a worksheet. For now I think I would like to gage from worksheet open to print - but I could also start with entry into the A9 cell.

Will I need to place the code into Worksheet_SelectionChanged or Worksheet_Print(if this exists) Then how do I retreive a time? I thought I could use Now() but this is a little more info than I need.

Any suggestions??

Bob Phillips
11-18-2005, 08:54 AM
OK, another simple question for the masters:thumb

I want to record the time spent on a worksheet. For now I think I would like to gage from worksheet open to print - but I could also start with entry into the A9 cell.

Will I need to place the code into Worksheet_SelectionChanged or Worksheet_Print(if this exists) Then how do I retreive a time? I thought I could use Now() but this is a little more info than I need.

Any suggestions??



Option Explicit

Private nTime As Date

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Time used so far: " & Format(Now - nTime, "hh:mm:ss")
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

samohtwerdna
11-18-2005, 09:12 AM
That was way eaiser than I thought it would be!!

Nice to know that Date just needs to be formated to get the time - (Should have known that :doh:)

OK - now if I did want to check from entry into Range("A9") would I just change

Private Sub Workbook_Open()
nTime = Now
End Sub
to

Private Sub Worksheets_SelectionChange(frstEnt as Range)
frstEnt = ("A9")
nTime = Now
End Sub

Or something like it??

mdmackillop
11-18-2005, 10:04 AM
Might also be worth looking at the Journal in Outlook, which can record this information for all workbooks.
Regards
MD

Bob Phillips
11-18-2005, 12:32 PM
That was way eaiser than I thought it would be!!

Nice to know that Date just needs to be formated to get the time - (Should have known that :doh:)

OK - now if I did want to check from entry into Range("A9")

Check what?

samohtwerdna
11-18-2005, 12:35 PM
The time spent from data entry into the A9 cell to the print function