Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Input Timer
I would like have code in the sheet module that calculates the elapsed time
it takes a user to enter data into the spreadsheet. For example... A user first data entry is in column "A" and last data entry is in column "E". I would like to start a clock when they enter the data in column "A" and stop it when the data in column "E" is entered and output to elapsed time in column "F". Data will continue to be entered in descending order by row every hour or so... that's why I believe the code needs to be dynamic in the sheet module. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time 1 2 3 4 5 0:01:42 1 2 3 4 5 0:02:12 1 2 3 4 5 0:02:42 1 2 3 4 5 0:03:12 Any suggestions would be appreciated... thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Input Timer
The below will point you in the right direction...To make this robost you
will need to check the row number while picking the start time and validate that when you output the time interval..Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. 'Format ColF to excel time format Private Sub Worksheet_Change(ByVal Target As Range) Static myTime As Variant If Target.Count = 1 Then If Target.Column = 1 Then myTime = Now ElseIf Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 Application.EnableEvents = True End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: I would like have code in the sheet module that calculates the elapsed time it takes a user to enter data into the spreadsheet. For example... A user first data entry is in column "A" and last data entry is in column "E". I would like to start a clock when they enter the data in column "A" and stop it when the data in column "E" is entered and output to elapsed time in column "F". Data will continue to be entered in descending order by row every hour or so... that's why I believe the code needs to be dynamic in the sheet module. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time 1 2 3 4 5 0:01:42 1 2 3 4 5 0:02:12 1 2 3 4 5 0:02:42 1 2 3 4 5 0:03:12 Any suggestions would be appreciated... thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Input Timer
Works Great... Thanks!
"Jacob Skaria" wrote: The below will point you in the right direction...To make this robost you will need to check the row number while picking the start time and validate that when you output the time interval..Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. 'Format ColF to excel time format Private Sub Worksheet_Change(ByVal Target As Range) Static myTime As Variant If Target.Count = 1 Then If Target.Column = 1 Then myTime = Now ElseIf Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 Application.EnableEvents = True End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: I would like have code in the sheet module that calculates the elapsed time it takes a user to enter data into the spreadsheet. For example... A user first data entry is in column "A" and last data entry is in column "E". I would like to start a clock when they enter the data in column "A" and stop it when the data in column "E" is entered and output to elapsed time in column "F". Data will continue to be entered in descending order by row every hour or so... that's why I believe the code needs to be dynamic in the sheet module. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time 1 2 3 4 5 0:01:42 1 2 3 4 5 0:02:12 1 2 3 4 5 0:02:42 1 2 3 4 5 0:03:12 Any suggestions would be appreciated... thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Input Timer
You are correct, I did some testing and it needs to be a bit more robust and
I need to add some additional criteria. I would like it to only start the timer if the cell in column "A" is blank and stop the timer when the data is entered into column €śE€ť one row below the row where the timer started€¦ in other words the data input range is 5 columns by two rows. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time Start Timer 2 3 4 5 6 7 8 9 Stop timer 0:02:12 "Jacob Skaria" wrote: The below will point you in the right direction...To make this robost you will need to check the row number while picking the start time and validate that when you output the time interval..Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. 'Format ColF to excel time format Private Sub Worksheet_Change(ByVal Target As Range) Static myTime As Variant If Target.Count = 1 Then If Target.Column = 1 Then myTime = Now ElseIf Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 Application.EnableEvents = True End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: I would like have code in the sheet module that calculates the elapsed time it takes a user to enter data into the spreadsheet. For example... A user first data entry is in column "A" and last data entry is in column "E". I would like to start a clock when they enter the data in column "A" and stop it when the data in column "E" is entered and output to elapsed time in column "F". Data will continue to be entered in descending order by row every hour or so... that's why I believe the code needs to be dynamic in the sheet module. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time 1 2 3 4 5 0:01:42 1 2 3 4 5 0:02:12 1 2 3 4 5 0:02:42 1 2 3 4 5 0:03:12 Any suggestions would be appreciated... thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Input Timer
Private Sub Worksheet_Change(ByVal Target As Range)
Static myTime As Variant, myRow As Long If Target.Count = 1 Then If Target.Column = 1 Then If myTime = "" Then myTime = Now: myRow = Target.Row ElseIf Target.Column = 5 Then If myRow = Target.Row - 1 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 myTime = "" Application.EnableEvents = True End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: You are correct, I did some testing and it needs to be a bit more robust and I need to add some additional criteria. I would like it to only start the timer if the cell in column "A" is blank and stop the timer when the data is entered into column €śE€ť one row below the row where the timer started€¦ in other words the data input range is 5 columns by two rows. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time Start Timer 2 3 4 5 6 7 8 9 Stop timer 0:02:12 "Jacob Skaria" wrote: The below will point you in the right direction...To make this robost you will need to check the row number while picking the start time and validate that when you output the time interval..Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. 'Format ColF to excel time format Private Sub Worksheet_Change(ByVal Target As Range) Static myTime As Variant If Target.Count = 1 Then If Target.Column = 1 Then myTime = Now ElseIf Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 Application.EnableEvents = True End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: I would like have code in the sheet module that calculates the elapsed time it takes a user to enter data into the spreadsheet. For example... A user first data entry is in column "A" and last data entry is in column "E". I would like to start a clock when they enter the data in column "A" and stop it when the data in column "E" is entered and output to elapsed time in column "F". Data will continue to be entered in descending order by row every hour or so... that's why I believe the code needs to be dynamic in the sheet module. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time 1 2 3 4 5 0:01:42 1 2 3 4 5 0:02:12 1 2 3 4 5 0:02:42 1 2 3 4 5 0:03:12 Any suggestions would be appreciated... thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Input Timer
It works nice for the timer. However, the start timer only on a blank cell
doesn't seem to work... that's OK for now... I need to do some real time testing with the users to see if it is an issue or not. Thanks for you help!!! "Jacob Skaria" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Static myTime As Variant, myRow As Long If Target.Count = 1 Then If Target.Column = 1 Then If myTime = "" Then myTime = Now: myRow = Target.Row ElseIf Target.Column = 5 Then If myRow = Target.Row - 1 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 myTime = "" Application.EnableEvents = True End If End If End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: You are correct, I did some testing and it needs to be a bit more robust and I need to add some additional criteria. I would like it to only start the timer if the cell in column "A" is blank and stop the timer when the data is entered into column €śE€ť one row below the row where the timer started€¦ in other words the data input range is 5 columns by two rows. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time Start Timer 2 3 4 5 6 7 8 9 Stop timer 0:02:12 "Jacob Skaria" wrote: The below will point you in the right direction...To make this robost you will need to check the row number while picking the start time and validate that when you output the time interval..Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. 'Format ColF to excel time format Private Sub Worksheet_Change(ByVal Target As Range) Static myTime As Variant If Target.Count = 1 Then If Target.Column = 1 Then myTime = Now ElseIf Target.Column = 5 Then Application.EnableEvents = False Target.Offset(, 1) = DateDiff("s", myTime, Now) / 86400 Application.EnableEvents = True End If End If End Sub -- If this post helps click Yes --------------- Jacob Skaria "Ndel40" wrote: I would like have code in the sheet module that calculates the elapsed time it takes a user to enter data into the spreadsheet. For example... A user first data entry is in column "A" and last data entry is in column "E". I would like to start a clock when they enter the data in column "A" and stop it when the data in column "E" is entered and output to elapsed time in column "F". Data will continue to be entered in descending order by row every hour or so... that's why I believe the code needs to be dynamic in the sheet module. Input 1 Input 2 Input 3 Input 4 Input 5 Total Input Time 1 2 3 4 5 0:01:42 1 2 3 4 5 0:02:12 1 2 3 4 5 0:02:42 1 2 3 4 5 0:03:12 Any suggestions would be appreciated... thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro with dynamic input | Excel Worksheet Functions | |||
Stopping a Timer / Running a timer simultaneously on Excel | Excel Discussion (Misc queries) | |||
Where is the FAQ? (for dynamic input lists) | Excel Programming | |||
Input Box Timer | Excel Programming | |||
Dynamic Macro Input | Excel Programming |