Solved

Recording the time in Excel

Posted on 2003-12-05
8
318 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 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month10 days, 1 hour left to enroll

624 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