vba Code for stopwatch

Question

I asked this questioin in another forum, but I am not sure that it made it through. I have created a stopwatch in Excel that is resident in each worksheet. I record times of presentations for the workshops that I conduct for public speaking. The stopwatch starts and stops with the code I have. My difficulty is that when I enter other data elsewhere on the worksheet, the stopwatch stops timing.I hope that someone in the forum might be able to tell me what additional vba code I need to add so that the stopwatch will continue to time while I keyboard comments onto the worksheet. I included the code that I have below.

Thanks,

Confused by Code

Dim stopped

Dim StartTimer

Public Sub startClock()

Dim start

    start = Timer

    Do While stopped = False

        DoEvents

        Worksheets("Sheet1").Range("F6").Value = Int((Timer - start + 0.5) / 60)

        Worksheets("Sheet1").Range("H6").Value = (Timer - start) Mod 60

        Worksheets("Sheet1").Range("I6").Value = (Timer - start + 0.5) - (Int(Timer - start + 0.5))

    Loop

End Sub

Private Sub cmdStart1_Click()

    startClock

End Sub

Private Sub cmdStop1_Click()

    stopped = True

End Sub

Private Sub CommandButtonStart_Click()

End Sub

Private Sub CommandButtonStop_Click()

End Sub

Answer 1

We add a single line of code:

Sub StopTiming()

MsgBox Format((Now - T1), "hh:mm:ss")

Sheets("Sheet2").Range("A1").Value = Format((Now - T1), "hh:mm:ss")

End Sub

Change the Sheet and cell to suit your needs.

Answer 2

I truly appreciate the help. I am able to obtain the msgbox. I need the time to record on the worksheet. What would I need to replace the msgbox code with to achieve this end.?

Thanks

Answer 3

Put the macro in a standard module:

1. ALT-F11  brings up the VBE window
2. ALT-I

    ALT-M opens a fresh module

3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

   1. bring up the VBE window as above

   2. clear the code out

   3. close the VBE window

To use the macro from Excel:

   1. ALT-F8

   2. Select the macro

   3. Touch RUN

You should leave your macros (and associated buttons) in place until you decide which approach is best

Manually run StartTiming.  Then do some worksheet actions.  Then manually run StopTiming.

You can always assign these macro to buttons or other controls later.

Answer 4

I am sorry to be clueless, but where would I add this code to the code that I currently have, or would this code replace the code that I have?

Thanks!

Answer 5

Rather than trying to simultaneously update time in cells and allow user interaction, something simple like:

Dim T1 As Double

Sub StartTiming()

T1 = Now

End Sub

Sub StopTiming()

MsgBox Format((Now - T1), "hh:mm:ss")

End Sub

will give you the elapsed time with one second granulaity.

If you really need a running clock in the cells, then consider using an OnTime event macro in addition:

http://www.cpearson.com/excel/OnTime.aspx