Solved: determine how long user was on a sheet

[slang]

Hello Excel gods

I have a survey the user inputs data into which we would like to know how long the user took to complete it.
The survey is on a hidden sheet and invoked through code and can only be exited by completing the required fields and choosing a complete button.

So my logic is that if the user actually completed the survey correctly it would take longer than just answering yes to everything just to get it out of the way.

So I was thinking about code in the worksheetactivate event that would set the start time,
[VBA]Static StrtTime As Date
Static Counter As Date
StrtTime = Now[/VBA]

and another in the complete button code,
[VBA]Counter = Now - StrtTime[/VBA]
that determined the elapsed time spent on the sheet.

First time with static variables
There is probably a better way....
Thanks all.

[Kenneth Hobs]

I would use Timer. Set t1 as a Private double type variable so that it can be retrieved.

e.g.[VBA]Sub FillARange()
Dim r As Range, bRange As Range, t1 As Double
Cells.ClearContents
t1 = Timer
'SpeedOn
[A1] = "ColA"
[B1] = "ColB"
[C1] = "ColC"
Set bRange = Range("A2:C1000")
For Each r In bRange
If r.Column = 1 And r.Row Mod 2 = 0 Then
r.Value = "Delete"
Else: r.formula = "=Row()*Column()"
End If
Next r
'SpeedOff
MsgBox "Added " & bRange.Rows.Count & " rows and " & bRange.Count & " cells." & _
vbCrLf & "It took " & CStr(Timer - t1) & " seconds."
End Sub[/VBA]

[snb]

or to register in minutes:

[vba]
Private Sub Workbook_Open()
ThisWorkbook.CustomDocumentProperties.Add "start", False, msoPropertyTypeDate, Now
End Sub

Private Sub commandbutton1_Click()
MsgBox DateDiff("n", ThisWorkbook.CustomDocumentProperties("start").Value, Now)
End Sub
[/vba]

[slang]

I would use Timer. Set t1 as a Private double type variable so that it can be retrieved.

e.g.[vba]Sub FillARange()
Dim r As Range, bRange As Range, t1 As Double
Cells.ClearContents
t1 = Timer
'SpeedOn
[A1] = "ColA"
[B1] = "ColB"
[C1] = "ColC"
Set bRange = Range("A2:C1000")
For Each r In bRange
If r.Column = 1 And r.Row Mod 2 = 0 Then
r.Value = "Delete"
Else: r.formula = "=Row()*Column()"
End If
Next r
'SpeedOff
MsgBox "Added " & bRange.Rows.Count & " rows and " & bRange.Count & " cells." & _
vbCrLf & "It took " & CStr(Timer - t1) & " seconds."
End Sub[/vba]

Excellent!
Thanks so much, worked perfect....