Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Recording the time in Excel

Posted on 2003-12-05
8
Medium Priority
?
319 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1800 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 200 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Suggested Courses

721 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