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

FootBallBat

Board Regular
Joined
Jan 26, 2012
Messages
162
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
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

V_Malkoti

Well-known Member
Joined
Jun 10, 2015
Messages
898
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.

Code:
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.
 
Last edited:

FootBallBat

Board Regular
Joined
Jan 26, 2012
Messages
162
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

Well-known Member
Joined
Jun 10, 2015
Messages
898
Give this a try. I've added code to handle multiple clicks in this one.

Code:
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.
 
Last edited:

FootBallBat

Board Regular
Joined
Jan 26, 2012
Messages
162
YES It worked.
I had to open up an new workbook, I must have been doing something wrong. Thanks for ll your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top