PDA

View Full Version : Stopwatch In Excel



THENUTS
04-18-2013, 01:49 AM
Hi,

This is my first post so please forgive my ignorance if i posted in the wrong place or need to do something different.

I would like to install a stopwatch in excel which starts automatically when the value in a certain cell is x . So for example if cell y3 was populated with a value then the stopwatch begins. I found this on the forum :

/kb/getarticle.php?kb_id=242

(please add vbaexpress.com to the above as i could not post a link due to being new)

Its great but you have to double click on a value in a cell to get the stopwatch going and i also don't want to put the stopwatch in column A.

Would anyone be able do adapt this code so that the stopwatch would begin in a column of my choice if a cell was populated with a value ?

I can use Excel on a basic level but do not understand Macro's.

Thank you in advance for any help which would be much appreciated. If i posted in the wrong place apologies and could anyone advise me where the best place to post would be if this is wrong.

Many Thanks

p45cal
04-18-2013, 03:18 AM
Often a worksheet_change event can be used, but before I adapt Zack's code, what causes the value in the cell to change? This matters because not all changes on a sheet trigger the worksheet_change event (!).
Also, does the cell you want ti watch have a formula in it?

THENUTS
04-18-2013, 03:45 AM
thanks for your initial response p45cal.

The cell which triggers the start of the clock would be populated from another cell which does indeed contain a formula.

What i have is cell AC70 which is a sum of a range of cells.

I would like the cell which starts the clock to have a formula in it like IF(AC70>1.15,5,"")

Once the number 5 appeared in the cell the clock would begin. I'm fine with simple formulas like the one above but its the code i can't do.

I think that answers your questions. Any help would be hugely appreciated

p45cal
04-18-2013, 04:35 AM
It almost answers my question.. how are the cells the formula looks at being changed? I suspect that you might be using some kind of external DDE formula that updates these cells.. if so we have to use something other than a worksheet_change event. If the data is input manually, no problem.

THENUTS
04-18-2013, 05:08 AM
Hi,

Cell AC70 is the sum of a number of cells above this cell.

The cells above AC70 are populated from an external source but i dont think that this impacts your question but i mention it because i'm not sure.

Once the cells above AC70 are populated with data that is the point where i would like another cell of my choice to be populated with a formula something like IF(AC70>1.15,5,"") with 5 ( or whatever ) being the trigger for the start of the clock in one second increments.

Thanks again for your help.

p45cal
04-18-2013, 05:17 AM
I will do this today, but I have a few other things to do first, so bear with me.

THENUTS
04-18-2013, 05:31 AM
Thank you !

p45cal
04-18-2013, 04:21 PM
A trial with just a worksheet_change event first.
Attached is a file with C3 highlighted, containing the formula
=C1
The idea being that C3 is watched, but you can change its value by changing the C1 value manually.
Observe the stopwatch in cell E1 when you make changes to C1.
It's sensitive to values >=1 when the stopwatch will run, and below 1 when the stopwatch should stop.
You can reset to 0 by manually entering 0 into E1, but only when the clock is stopped. You can reset to any time you want manually, not just 0.
If this works OK in the attached, then you can move on to test it in your spreadsheet. First copy the following code into the sheet code module of the sheet which has the watched cell.Private Sub Worksheet_Change(ByVal Target As Range)
If Not Blocked Then
If Range("C3") >= 1 Then 'we want to start or continue running
If Not running Then
running = True
StartTime = Now - Range("E1").Value
UpdateTimer
End If
Else 'we want to stop running
If running Then
running = False
Range("E1").Value = Now - StartTime
End If
End If
End If
End Sub
Next copy the following code into a standard code module of the same workbook:Public running As Boolean
Public Blocked As Boolean
Public StartTime
Sub UpdateTimer()
If running Then
Blocked = True
Range("E1").Value = Now - StartTime 'E1 hasn't been qualified so the active sheet needs to remain the sheet with the stopwatch on - to be tweaked later.
Blocked = False
Application.OnTime Now + TimeValue("00:00:01"), "updatetimer"
End If
End Sub
Adjustments you can make to the code are:
Change every instance of Range("E1") in both bits of code to the cell on the sheet where you want the stopwatch to be.
Change the only instance of:
If Range("C3") >= 1
to change (a) which cell is looked at and (b) what conditions to start the stopwatch.

I suspect this won't work in your sheet because of how the cells are updated, anyway, over to you to tell me if it does or not, then we can use a different event to make it work.

THENUTS
04-19-2013, 12:53 AM
Huge thanks - Your attachment does exactly what i need. I am going to try and integrate it into my spreadsheet this morning and will let you know how i get on.

I really appreciate what you have done. I will let you know how i get on.

THENUTS
04-19-2013, 03:01 AM
Hi p45cal,

I have tried for the last 2 hours to get this to work and i have failed.
I don't think its your code i think its just beyond the scope of my ability to integrate it into my worksheet.

Your help so far has been beyond what i expected for free.

Please forgive me if this is rude , inappropriate or asking too much (just say if it is ) but would it be possible for you to insert the code into my workbook ( attached ) ? I am perfectly happy to pay you for this as you have already taken some time to get to where we are now - just let me know your fee.

If this is possible what i need is the stopwatch to be in cell I4 on each of the sheets and for the stopwatch to start only if cell AC72 is greater than 1.00

I need each sheet to work independently of the other so if one sheet has the stopwatch activated the next sheets stopwatch will not start until cell AC72 in that sheet is greater than 1.00.

I had lots more tabs but had to delete loads due to the file size but im guessing i can just add those back in and do some copying and pasting if you are able to get it to work.

As i said i am happy to pay you for this - just let me know

THENUTS
04-20-2013, 01:56 AM
Hi pcal45,

Just wanted to see if you are still picking up this thread. Did you see my last post ?

p45cal
04-20-2013, 03:12 AM
Hi pcal45,
Just wanted to see if you are still picking up this thread. Did you see my last post ?Yes, I saw it. Multiple stopwatches is a bit more involved but do-able. I'll Private Mesage you at this site in a moment.

p45cal
04-23-2013, 02:52 AM
Should anyone be interested, we solved this one by moving the event code from the worksheet code-module to the workbook code-module and having the three variables which were global (running, StartTime and Blocked) moved to sheet-scoped defined Names so that each sheet had its own version of these variables, while tweaking the UpdateTimer sub to take an argument - the sheet concerned's name.