VBA for Stopwatch in a column

[REUBEN]

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:

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

 

[HackSlash]

You would need a Boolean variable on sheet1 for this to work. Just change it to point to wherever you have your stopped variable. This is to keep track of when the stop watch is stopped.

stopped = False

 

[REUBEN]

I'm not sure I understand what you are suggesting. I've used the code you have provided. And when I double click the breaktime field is when the user form is jumping up.... but it is after I click the start break or the command 1 button or the end break command 2 buttons that I get the error message.

Also what do yo umean by

Just change it to point to wherever you have your stopped variable.

 

[HackSlash]

I am using a qualified reference to the "stopped" variable because we are manipulating this variable from the form. It's not a local variable to this subroutine. You need to change "sheet1." to match wherever your main code is. You may have changed the name of the sheet, for example. I can't tell you how to fix it without seeing your workbook and I think it's best if you understand what we are doing and why.

If you get this working you will notice that the code I posted doesn't count properly. Once you get the sheet reference figured out you will have to modify the line that subtracts the current time from "start" because the "start" variable exists in the sheet code, not in the form code.

EXAMPLE:

UserForm1.TextBox1.Text = Format(Sheet1.start - Time, "hh:mm:ss")

(Where "Sheet1" is the worksheet object that contains the variable "start" which holds the start time)

Does that make sense?

 

[HackSlash]

My bad, you might also need to declare your variables as public so they are available outside of the sheet code:

These would be declared at the top of your sheet module code, before any subroutine

Public stopped
Public start

 

[REUBEN]

That did it! The declaring of stopped and start as public, set everything in place.

Here's a link to my worksheet - http://s000.tinyupload.com/index.php?file_id=86357634065335921325.

I have also added some lines of code to auto inster help texts in the next fields that the user must click in.

I have a few comments. Now that everything is working almost fine, the Userform is not really adding much value as the text box in the form doesnt show the Stop clock counting. Infact on clicking yes, there is no reaction. Although the stopclock is working in the background. One can only see this if we stop the form then double clicked the same break time field to open the userform again. At the moment the user does not get any indication of whether the stop clock is counting or not.

Next, I am trying to add in help text in every subsequent field that the user must click in. And I could use the help text in the Break Time field as I have done for the end time field and the next start time field for the next row. But using the same code for the break time field was messing with the calculation of the Time Used field, I had to chose to leave the break time field blank. Any suggestions on how can I still have the help text like "Double click When Taking a Break" or something like that be prefilled in the break time field for the user to see and click on.

Lastly, there needs to be a little error handling done with the Userform. As of now if I double click in the break time field and get to the userform, if I chose to click the form close 'X' mark on the top right, it closes the form but has recorded the time the form was open for as the time in the break time field. Secondly, I would like it to not start the break time calculation/ user form if I were to click another empty cell in the break time column. As right now the number of times I click in the break time column, the user form is coming up. This should be unique to each row as it is supposed to be dedicated for that very row (task) only.

I hope the above makes sense.

 

[HackSlash]

I am attempting to give you some structure to work with. You should be able to move the code around and make the program fit whatever criteria you have in the future. Play around with it.

The way I have it in my spreadsheet the clock does count in the textbox. You were right about the start time being not being controlled by the button. So I moved the statement "Sheet1.start = Time" inside the form button.

Please take some time to really understand the code and why I have made each change. You should be able to clean this up in to something that fits your needs.

Sheet1

Public start
Public stopped

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
            UserForm1.Show
            If stopped Then
                Target.Value = Format(Time - start, "hh:mm:ss")
                Target.Offset(0, 1).Select
            End If
        Case Else
         'do nothing
    End Select
End Sub

UserForm1

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

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

 

[REUBEN]

Thank you for your reply and all the good suggestions. I am trying my best to understand what the codes are doing and trying to playing around a little to help adjust specific actions.

I would however need your help to figure out the below:

1) A little error handling is required in the code; because as of now, if a user clicks on End Time or Break Time fields by mistake the code throws up and error message (Generating from the end time calculation) as there is no Start Time entry. I'd imagine something similar may happen if one were to just click on the break time field without any entries in start and end time fields. Can there be a way to ensure that the user always double clicks or is directed to click the Start time field first, then the end time and or break time and finally the end time. And then the calculation. With some help I had created a the below code in the past for another sheet. I’ve tweaked it to do what I think will work for this sheet, (and I could be completely wrong!):

If Target = "3" Or "4" Then
    MsgBox "You Can Only Begin With Clicking The Start Time Field."
   
    Exit Sub
    End If
   
    Loop Until Target = "2"

But I do not know where or how to insert this code.

2) I’ve incorporated your new codes in to my workbook. And have made the finer changes too. But then the Breakwatch function doesn’t work now. Also, there's nothing showing in the text box as it was the case before making these changes. Therefore, it appears like the Time Used field is not being updated at the end. The end result is that the break time field is updated with an entry 00:00:00.
3) Lastly, dont you think that it may be easy to simply just use an IF function within the Time Used column to calculate the total time used? As then I can use the ‘Help Text’ to auto populate in the Break Time field as well as have the possibility of adding any additional breaks taken during the course of that very task which will of course be calculated with the help of your breakwatch function. Your thoughts?

Thank you once again.

 

[HackSlash]

1. I've clicked on everything in every order. It never throws an error. Each field works independently. You can click them in any order but if you make the end time before the start time you get "###" in the elapsed

You could need to qualify each handler if you want to force an order. The "Select Case" statement is where we evaluate where the cursor is. So in the "Case EndCell" You could encapsulate that whole block with an If statement that evaluates if the Start Field in that row is blank.

2. Breakwatch works fine. The second button starts the counter. The first button stopped the counter. If you stop the counter before you start then it will close. To fix this behavior you just need to set the stopped flag in the start button;

Sheet1.stopped = False

If you don't see text in your textbox then you must ensure that the code matches the name of the textbox "UserForm1.TextBox1.Text"

3. Just do it. There is a comment on the line that sets the value of the elapsed time "'Calculates the total time elapsed". Remove that line and put your formula in

=IF(B2>0,(C2-B2)-D2,"")


If you don't understand the code then look up each new command on google. I won't be able to make every update to your worksheet for you.

Read this whole guide on debugging VBA code before coming back to this forum. It will help you greatly:

Debugging VBA

 

[REUBEN]

Hi HackSlash,

I am thankful for all your suggestions and appreciate all the help you have provided me with the codes. I just figured where the mistake was. I had taken the command button 1 as the start button and command button 2 as the stop button. Whereas in reality they are the other way round. I just switched them around and the breawatch is working perfectly. I can see the counter and when I stop it I get the correct value in the break time field. My bad for that silly mistake.

I'm still need to figure out the error handling to ensure the user clicks the correct fields in the right order. So will try some pemutations with that.

But here's thanking you once again for all your help.

R!

 

[REUBEN]

Hi HackSlash,

The sheet has been doing very well.

I've customized it to suit us best. I'm now stuck with two questions. In case a user is in the break time running the Stopwatch (counter), and they need to now enter the start time for another task (in the next row). as the counter is running, one cannot exit it.

Secondly, and this may sort of link up to the previous question. If one has used the break time counter once and there is a value in the break time field, what should the code be tweaked to make the break watch continue counting or create another break time count?

The reason for these questions is that users are working on different tasks/ cases at the same time, and need to switch between tasks every now and then while still keep track of the time they end up spending on each task/case finally.

Any suggestions?

Thank you.