VBA for Stopwatch in a column

REUBEN

Board Regular
Joined
Mar 7, 2014
Messages
113
HI All,

I've been reading up about adding a stopwatch VBA in my sheet and make it really simple to use.

The sheet is to record time one is spending on their specific tasks. There is start time column, end time colum. And now I'm trying to add a break time column. I am using the following code to enter the start and end times by simply double clicking in the cells of the start time and end time columns:

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    If Intersect(Target, Range("$B$2:$C$500")) Is Nothing Then Exit Sub
    Target = Time
    Cancel = True
End Sub

The idea is to have the option of starting a stopwatch counter by double clicking a cell next to the latest cell I've entered the start time in. and then double click that same cell to stop the stopwatch counter. Then when I'm done with the task finally, I double click the cell in the end time column.

Two questions I have are:
1) how to combine the double click feature as to a stop clock as I already am using the "Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)" code.
2) how to tweak the above code to only capture the hours and minutes (HH:MM) and not the seconds(SS).

Thank you.
Reuben
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
1. You need to analyze the cell and have a different action for each cell. Use select case like this:

Code:
Select Case ActiveCell.Address 
    Case "$F$2" ' Address of start cell
         Target = Time
    Case "$H$2" ' Address of end cell
         Target = Time - Range("$F$2").Value ' Address of start cell
    Case Else 
         'do nothing
    End Select

2. Use Format Function

Code:
Select Case ActiveCell.Address 
    Case "$F$2" ' Address of start cell
         Target = Format(Time, "hh:mm")
    Case "$H$2" ' Address of end cell
         Target = Format(Time, "hh:mm") - Range("$F$2").Value ' Address of start cell
    Case Else 
         'do nothing
    End Select

3. Remove this line:

Code:
If Intersect(Target, Range("$B$2:$C$500")) Is Nothing Then Exit Sub
 
Upvote 0
UPDATE:
I had a moment to test this code and I got it to work. This new one will write an end time and the time elapsed in the cell to the right of the end time. Change the start and end variables to match the cells you want.

Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim StartCell As String
    Dim EndCell As String
    
    StartCell = "$B$2"
    EndCell = "$C$2"
    
    Select Case ActiveCell.Address
        Case StartCell ' Address of start cell
            Target.Value = Format(Time, "hh:mm")
        Case EndCell ' Address of end cell
            Target.Value = Format(Time, "hh:mm")
            Target.Offset(0, 1).Value = Format(Range(EndCell).Value - Range(StartCell).Value, "hh:mm") 'Calculates the total time elapsed
        Case Else
         'do nothing
    End Select
End Sub
 
Upvote 0
Hi HackSlash,

Thank you for your reply and apologies for the delay in responding.

You code is working fine but it meets its end after the 2nd row. Also it doesnt quite achieve what I'm aiming for. For some reason one of my detailed reply's to your previous suggestion did not get posted to on this thread. Not sure why!

Allow me to explain my sheet once again and tell you what i'm trying to achieve. Here's how my sheet looks and some of the basic IF formulas that autopopulates data.

DateStart TimeEnd TimeBreak TimeTime UsedComments
=IF([@[Start Time]]="","",[@[Start Time]])=IF(B2="","", IF(C2="","",IF(C2 & B2="","",C2-B2-D2)))task 1 - specific comments....

<tbody>
</tbody>

At the moment I am able to double click in the start and end columns (using my original VBA code) and then manually add in the break time. And the IF formula in the Time Used column calculates the time spent on that particular task. These formulas are copied down on to the subsequent rows. The idea is to use this sheet for various tasks and record my time spent on those miscellaneous tasks.

And to give you an example of how the above table looks when in use, here a quick look again:

DateStart TimeEnd TimeBreak TimeTime UsedComments
25 November 201608:30:0010:30:0000:15:0001:45:00task 1 - specific comments....

<tbody>
</tbody>

Now the idea is to be able to double click the start and end times of subsequent tasks and use like a stopwatch feature in the break time column. And that the stopwatch would start when a user doubleclicks in that field and stop when doubleclicked again. And finally the IF formulas will calculate the time used.

Hope I've explained this okay.

Thanks again for your help.

Reuben
 
Last edited:
Upvote 0
It is not clear what "break time" is. Don't you need "Break Start" and "Break End" columns? Then you just add to the select case statement I gave you to have the same start and end times on these new cells.
 
Upvote 0
Well, that's the idea. To Not have two columns for calculating the break. One must simply be able to use the field in the break time column next to the start and end time fields for the specific times they are not working on the task or are taking a break. And that is why I was thinking of inserting a stopwatch feature in that field.

Do let me know if you have an alternative solution for what I am trying to do. Since very basic users will use these sheets, I am trying to make this as easy as possible to give them an idea of an incentive to use it. and that is simply clicking in the fields to enter start and end times and also clicking in the break time. But as they can take or have multiple breaks I'm thinking on the stopwatch feature.

Thanks again.
 
Upvote 0
Ok, to make this easier on people you should consider adding form buttons, start, stop, break, etc.

I made a little thing that works on one form button. It counts time in the cell and then at the end it puts the total elapsed time in that same cell. Just point a form button at this sub:

Code:
Sub StopWatch()
    If stopped Then
        stopped = False
        start = Time
        Range("D2").NumberFormat = "@"
        Do While stopped = False
            DoEvents
            Range("D2").Value = Format(start - Time, "hh:mm:ss")
        Loop
        Range("D2").Value = Format(start - Time, "hh:mm:ss")
    Else
        stopped = True
    End If
End Sub
 
Last edited:
Upvote 0
Hi HackSlash,

thank you for the suggestions.

I've thought about this and tried a few macros and then read up and watched a lot more videos to see what's best. And of course the simplest solution is like you said to just have buttons for start times, break times and end times and let macros enter the times in each cell automatically.

Now, while this code that you provided earlier (and I've just added a date line), works fine for the first row. How can I have a different macro buttons trigger this for the subsequent tasks?
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    Dim StartCell As String
    Dim EndCell As String
    
    StartCell = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
    EndCell = ThisWorkbook.Sheets("sheet1").Cells(Offset(0, 1).Rows.Count, 1).End(xlUp).Row + 1
    
    Select Case ActiveCell.Address
        Case StartCell ' Address of start cell
            Target.Value = Format(Time, "hh:mm")
        Case EndCell ' Address of end cell
            Target.Value = Format(Time, "hh:mm")
            Target.Offset(0, 1).Value = Format(Range(EndCell).Value - Range(StartCell).Value, "hh:mm") 'Calculates the total time elapsed
        Case Else 'do othing
    End Select
End Sub

As mentioned earlier, the above code is only targetting the 2nd row, for subsequent tasks it would be good to have the code select the next empty row and then use that to enter the values there. I am unable to put a code together for this, due to the lack of programming knowledge.

Regarding the Stopwatch code you provided. I'm sorry but it doesn't seem to be working when I tried it. I tried several ways of inserting it and using it in my current sheet as well as new sheets but it just didn't work. I have also come across these codes that can be assigned to buttons to work. But again the code is assigned to run only on a specific field. So this needs tweaking as well.

The codes are:
Code:
Sub startTimer()Application.OnTime Now + TimeValue("00:00:01"), "Increment_count"
End Sub
Sub Increment_count()
Sheet1.Range("D2").Value = Range("D2") + 1
startTimer
End Sub
Sub stopTimer()
Application.OnTime Now + TimeValue("00:00:01"), "Increment_count", Schedule:=False
End Sub

Thank you for your reply.
 
Last edited:
Upvote 0
How can I have a different macro buttons trigger this for the subsequent tasks?

You can achieve this in a number of different ways. You could have a different set of buttons for each row but that's tedious. You could have the button work off of the currently selected cell but you will run in to a few different logistic problems there.

Honestly, I like the double click method you started with for making dynamic code because you can change your StartCell and EndCell be columns so that anywhere in that column will be live.

For example:

Code:
    StartCell = "2"
    EndCell = "3"
    BreakWatch = "4"
    
    Select Case ActiveCell.Column

Now, there are options for the stopwatch too. I like using a form for this. I made a solution using a pop up form but it's going to be hard to share if you don't understand how forms work. I might have to share my workbook.

In the sheet code:
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)    Dim StartCell As String
    Dim EndCell As String
    
    StartCell = "2"
    EndCell = "3"
    BreakWatch = "4"
    
    Select Case ActiveCell.Column
        Case StartCell ' Address of start cell
            Target.Value = Format(Time, "hh:mm:ss")
            Target.Offset(0, 1).Select
        Case EndCell ' Address of end cell
            Target.Value = Format(Time, "hh:mm:ss")
            Target.Offset(0, 2).Value = Format(Target.Value - Target.Offset(0, -1).Value - Target.Offset(0, 1).Value, "hh:mm:ss")  'Calculates the total time elapsed
            Target.Offset(0, 1).Select
        Case BreakWatch
            stopped = False
            start = Time
            UserForm1.Show
            Target.Value = Time - start
            Target.Offset(0, 1).Select
        Case Else
         'do nothing
    End Select
End Sub

and in the UserForm I have this:

Code:
Private Sub CommandButton1_Click()    
    Sheet1.stopped = True
End Sub

Private Sub CommandButton2_Click()
    Do
        DoEvents
        UserForm1.TextBox1.Text = Format(start - Time, "hh:mm:ss")
    Loop Until Sheet1.stopped = True
    UserForm1.Hide
End Sub

My userform has 2 buttons and a textbox for the counter. When you click the "end break" button it writes the elapsed time in to the cell that you double clicked.
 
Upvote 0
HI HackSlash,

Thank you for your suggestions. I've just now been able to try out your codes. The start and end times work fine but the break time seems to have errors. I've had a little experience using userforms. But I created one that would fit what you said. When I double click in the break time field, the userform jumps up, see below:
9
direct link - http://i65.tinypic.com/az8b5t.jpg (once on this page, you'd have to click on the zoom button under tools to see the image)

and when I click the command button1 (I'm guessing this must be "start break", in your form), I receive an error. here's the error image:
9

Direct link - http://i64.tinypic.com/dpe901.jpg (once on this page, you'd have to click on the zoom button under tools to see the image)

Unsure of the cause of the error. But is seems it is at the ".stopped = True" code line.

Thank you for checking this.

Reuben
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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
Back
Top