How to start a timer when i click a Url to go to the next sheet

[FootBallBat]

I am creating a test in excel (cause I love excel).
Page 1 Has: "Start Test" In A1 (which then brings me to page 2 with the first question).
I would like a clock to start when the Url is clicked, the clock does not need to be displayed through each page but...
When the last question is answered and goes to the last page of the test with the answers I would like it to show the elapsed time that it took to finish the test.
So I would like the clock to start when "Start Test" is clicked and for it to end when "Finish Test" url is clicked on the last question page. Then have the time it took shown up on the results page.

I am currently running excel 2007...
Thanks in advance

 

[V_Malkoti]

Can you use macros? If yes, then try this code. Paste it in your ThisWorkbook module in VBA window, and adjust sheet name and cell adress according to your needs.

Private tmStartTime As Date, tmEndTime As Date


Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    If Target.TextToDisplay = "Start Test" Then tmStartTime = Now
    If Target.TextToDisplay = "Finish Test" Then
        tmEndTime = Now
        Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("[COLOR=#0000ff]A40[/COLOR]").Value = (tmEndTime - tmStartTime)
        Sheets("[COLOR=#0000ff]Sheet1[/COLOR]").Range("[COLOR=#0000ff]A40[/COLOR]").NumberFormat = "[h]:mm:ss;@"
    End If
End Sub

Please note that if user clicks on Start Test multiple times then this code will will consider most recent click time as start time. Same goes for Finish Test.

 

[FootBallBat]

Im having a little bit of trouble.
If you could help me a little more. I can run macros but dont do it often. I have "sheet1,sheet2,sheet3,sheet4".
Sheet1: A1= "Start Test" (link to Sheet2)
Sheet2: A1= "Next" (link to Sheet3)
Sheet3: A1= "Finish Test" (link to Sheet4)
Sheet4: A1= The elapsed time.

Hope this helps, and sorry for being a hassle.

 

[V_Malkoti]

Give this a try. I've added code to handle multiple clicks in this one.

Private tmStartTime As Date, tmEndTime As Date
Private boolStarted As Boolean, boolEnded As Boolean


Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    If Target.TextToDisplay = "Start Test" And boolStarted = False Then
        tmStartTime = Now
        boolStarted = True
        Thisworkbook.Sheets("Sheet4").Range("A1").Value = ""
    End If
    If Target.TextToDisplay = "Finish Test" And boolStarted = True And boolEnded = False Then
        tmEndTime = Now
        boolEnded = True
        boolStarted = False
        Thisworkbook.Sheets("Sheet4").Range("A1").Value = (tmEndTime - tmStartTime)
        Thisworkbook.Sheets("Sheet4").Range("A1").NumberFormat = "[h]:mm:ss;@"
    End If
End Sub

1. Press Alt+F11 to go to VBA window
2. In VBA window, press Ctrl+R to open Project Explorer.
3. Expand VBAProject node, then Microsoft Excel Objects node.
4. Double click on Thisworkbook node
5. Paste above code in the code pane opened on right.
6. Save you workbook, then try clicking on links and see if elapsed time is shown.

 

[FootBallBat]

YES It worked.
I had to open up an new workbook, I must have been doing something wrong. Thanks for ll your help.