[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Monitoring Spreadsheet usage

is it possible to record how long a user stayed in a spreadsheet regardless of whether they save it. So if the user clicks leaves the spreadsheet it records the time/date

Thanks
Seamus
0
Seamus2626
Asked:
Seamus2626
  • 7
  • 5
  • 3
3 Solutions
 
StephenJRCommented:
So if the workbook is closed irrespective of whether it was saved?
0
 
Seamus2626Author Commented:
Exactly

Thanks
Seamus
0
 
StephenJRCommented:
Seamus - what about this in the ThisWorkbook module? Suppose if you wanted it displayed elsewhere would need to rethink.
Dim t As Double

Private Sub Workbook_Open()

t = Now()

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox Now() - t

End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Seamus2626Author Commented:
Could that work with the below i wonder? I have this code but it relys on user saving ss which isnt practical in this particular report

Can you apply the above to the below?

Thanks
Seamus
Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("check")
    .Range("B64000").End(xlUp).Offset(0, 2) = Now()
End With
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()
With Sheets("check")
   .Range("B64000").End(xlUp).Offset(1, 0) = Environ("USERNAME")
   .Range("B64000").End(xlUp).Offset(0, 1) = Now()
End With
End Sub

Open in new window

0
 
StephenJRCommented:
Like this?
Dim t As Double

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Sheets("check")
    .Range("B64000").End(xlUp).Offset(0, 2) = Format(Now() - t, "h:mm:ss")
End With
ActiveWorkbook.Save
End Sub

Private Sub Workbook_Open()

t = Now()
With Sheets("check")
   .Range("B64000").End(xlUp).Offset(1, 0) = Environ("USERNAME")
   .Range("B64000").End(xlUp).Offset(0, 1) = Now()
End With
End Sub

Open in new window

0
 
Seamus2626Author Commented:
Thats good Stephen, its recording the time i spent in there, the only problem is the file is saving on exit, so no one gets a prompt to save. Dangerous errors could be saved, can the prompt to save be retained while keeping the functionality to record the time spent in the ss?

Thanks
Seamus
0
 
StephenJRCommented:
What about commenting out line 7?
0
 
gtglonerCommented:
Have a look at this file:
Book1.xls
0
 
Seamus2626Author Commented:
That didnt work without the save Stephen


gtgloner: that is what im looking for, but its not creating a list, i need it to keep building that data, so the next time it returns to  d2, then d3, so i can build a history of who was in the ss and for how long.

Thanks
Seamus
0
 
StephenJRCommented:
Seamus - how so? If a change is made you'll be prompted to save, no?
0
 
gtglonerCommented:
OK, I think I have it now:
Book1.xls
0
 
Seamus2626Author Commented:
Problem is guys, if the user doesnt save on either approach, the data wont be saved on reopening that file......
0
 
gtglonerCommented:
Sounds fine to me.
0
 
Seamus2626Author Commented:
Not sure why this question hasnt closed with the points assigned......
0
 
Seamus2626Author Commented:
thanks guys, i will be able to work the last part out

Cheers
Seamus
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now