Solved

Recording the time in Excel

Posted on 2003-12-05
8
316 Views
Last Modified: 2010-05-01
Hi, everyone!

I would like to have a small timer in the background of my Excel application. Whenever the user changes a value in any cell in Sheet1, in the same cell in Sheet2 should be written how much seconds it took the user between two changes - that is, e.g., the user writes a value in B4 and then, 12 seconds later, another value in C15. Then, in Sheet2, in cell C15 would stand simply "12". It doesn't really matter, if the programme writes seconds or the standart time format.
I'm looking for a complete solution, that's why I am giving away the maximum points!
0
Comment
Question by:luchko
  • 4
  • 2
  • 2
8 Comments
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9881443
hi luchko,

right click your sheet tab
View Code
paste the sub below

Public startt

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Range(Target.Address) = Timer - startt
startt = Timer
End Sub


Cheers

Dave
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 450 total points
ID: 9881465
a bit tidier on the formatting

Public StartT As Double
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Range(Target.Address) = Format(Timer - StartT, "##0.0 /secs")
StartT = Timer
End Sub
0
 
LVL 10

Assisted Solution

by:ADSaunders
ADSaunders earned 50 total points
ID: 9881517
Hi luchko,
Create a module in your workbook, and paste in the following:

Dim StartTime As Date
Sub ChangeTime()
    StartTime = Now()
End Sub
Sub ChangeCell()
    MsgBox (ActiveCell.AddressLocal)
    Set mc = Worksheets("Sheet2").Range(ActiveCell.AddressLocal)
    mc.Value = Now() - StartTime
    ChangeTime
End Sub

In the Workbook open event (to initialise the value):

Private Sub Workbook_Open()
    ChangeTime
End Sub

And in the Worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    ChangeCell
End Sub

Now, Sheet2 will contain times from opening the wokbook to entering the first cell, and the interval between cell changes.

Regards .. Alan
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 
LVL 10

Expert Comment

by:ADSaunders
ID: 9881520
luchko,
You don't really need a timer, the interval between one cell change and the next seems to be what you're looking for.
0
 

Author Comment

by:luchko
ID: 9881540
Thank you a great lot! Could you also please tell me if I could somehow use the usual format xx:xx:xx as well?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9881564
sure, try

Public StartT As Double
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Range(Target.Address) = Format(Now() - StartT, "hh:mm:ss")
StartT = Now()
End Sub

Cheers

Dave
0
 

Author Comment

by:luchko
ID: 9881606
Excellent! I am very grateful to both of you!
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 9881610
Thanks for the grade luchko
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
vb6 connector to SQL Server 2 37
How does CurrentUser work? 10 39
How to measure sizes and angles in scanned images ? 3 77
transition to visual .net from vb6 5 41
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question