Solved

Recording the time in Excel

Posted on 2003-12-05
8
310 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 10

Expert Comment

by:ADSaunders
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:luchko
Comment Utility
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
Comment Utility
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
Comment Utility
Excellent! I am very grateful to both of you!
0
 
LVL 50

Expert Comment

by:Dave Brett
Comment Utility
Thanks for the grade luchko
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now