Excel Stop watch

  sms 09:55 28 Nov 03
Locked

Is it possible to insert a stop watch into excel spreadsheet. I need it for running a programme.
Thanks

  Lozzy 09:59 28 Nov 03

sure if this will work but click here

You can use the system timer to give you time elapsed bewteen 2 events, but this may not be what you are after.

Can you explain your eventual requirements a little more?

  VoG II 16:25 28 Nov 03

It is possible using some VBA code. The following example counts down from 60 seconds and sisplays the time remaining:

Dim AlarmTime As Date, AlarmTime2 As Date

Sub Count_Down_Timer()

ActiveSheet.Range("A1").Value = 60

ActiveSheet.Range("B1").Value = "Counting"

Call TrapTime

Call TrapTime2

End Sub

Private Sub ShowTimeLeft()

ActiveSheet.Range("A1").Value = Second(AlarmTime - Now)

Call TrapTime2

End Sub

Private Sub TrapTime()

AlarmTime = CDate(Date) + TimeValue(Now()) + TimeValue("00:01:00")

Application.OnTime earliesttime:=AlarmTime, procedure:="StopTimer"

End Sub

Private Sub TrapTime2()

AlarmTime2 = CDate(Date) + TimeValue(Now()) + TimeValue("00:00:01")

Application.OnTime earliesttime:=AlarmTime2, procedure:="ShowTimeLeft"

End Sub

Private Sub StopTimer()

On Error Resume Next

Application.OnTime earliesttime:=AlarmTime2, procedure:="showtimeleft", schedule:=False

On Error GoTo 0

ActiveSheet.Range("B1").Value = "Done"

End Sub

Start Excel, ALT+F11 to open the Visual Basic Editor, Insert/Module then paste in the above code. Close the VBE. From your worksheet, Tools/Macro/Macros, highlight Count_Down_Timer and click the Run button.

VoG has given you a countdown stopwatch, the following code gives you a simple time elapsed stopwatch. The start time is placed in cell A1, when B1 is clicked the time elapsed since the start is displayed in the cell.

If you wish a second time based on the original start time then you will need to select a cell other than A1 or B1.

If you select A1 the sequence will be reset.

'==============================================

Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Resume Next

With ActiveCell

If .Row <> 1 Or .Column > 2 Then Exit Sub

If .Column = 1 Then

Range("B1:C1").ClearContents

ActiveCell.Value = Timer

ElseIf Range("A1") > 0 Then

Range("B1").Value = Timer - Range("A1")

End If

'==============================================

HTH

  VoG II 16:46 29 Nov 03

could I also change this code so that it acts like a stop watch


=========================================

What exactly do you mean "acts like a stop watch".

You should post back to this thread. The e-mail facility is for other things.

  VoG II 14:30 30 Nov 03

To just display elapsed time (as per your e-mail; please do not e-mail me)

Sub Swatch()

Do

ActiveSheet.Range("A1").Value = Now()

Application.Wait ("0:00:01")

Loop

End Sub

You need to format A1 to display time in the format you want.

Then run the above, Press ESC to start it ticking then press ESC when you want to turn it off.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Alex Chinneck’s giant ice cube Christmas tree at Kings Cross

Apple rumours & predictions 2017: The iPhone 8, new iPads, and everything else you should expect fr7…