Getting System Time

Judy · Mar 25th, 2002, 03:02 PM

I want to scan the students (they have barcode stickers) in our cross country event when they come back across the line. I want excel to get the barcode and grab the system time and correspond it to the barcode.
I don't want to use a macro as that is a keystroke after the scan. I can do it with a function using NOW() but every time I add another entry it updates all the times, volatile. How can I get to keep the time it first gets instead of updating all the time?

Tom Schreiner · Mar 25th, 2002, 03:33 PM

Please give some more detail as to how this information is being entered.
Which cells?
By way of DDE?
Formulas, as far as I know, cannot be made static.
I could be wrong about that...
A simple event capture would be fast enough.
Perhaps just as fast as a function.
If not as fast the time involved would be miniscule.
Please post more information.
Tom

Judy · Mar 25th, 2002, 04:04 PM

I scan the barcode at cell A2 and the barcode number goes in there. The B column has the following =IF(A2>0,NOW(),"")copied down to all cells. So when A2 has a value in it B2 gets the time. After the scan for A2, A3 cell is selected and ready for the next scan. Once cell A3 has a value in it B3 gets the time, but it also updates the previous time in A2 which is where the problem lies. Hope I have explained that little better.

Tom Schreiner · Mar 25th, 2002, 04:09 PM

place this in your sheet_change event

If Target.Column = 1 Then _
Cells(Target.Row, 2).Value = Now

Tom Schreiner · Mar 25th, 2002, 04:14 PM

Actually, disreguard that non-solution and try this:
The other way involves a keystroke

Place a formula in AA1
=A1

Copy down Col AA

When the info is scanned into A1
the calculation will raise an event which will immediately place now in B

Code should look like this

Private Sub Worksheet_Calculate()
If ActiveCell.Column = 1 Then _
Cells(ActiveCell.Row, 2).Value = Now
End Sub

try it out
Works with DDE, queries
I just created a workbook using real time and ran code based upon the calculate event.

Tom

PS

You mentioned that it scrolled down automatically to the next row?

You may need to use this code in place of the other

Try both

Private Sub Worksheet_Calculate()
If ActiveCell.Column = 1 Then _
Cells(ActiveCell.Row - 1, 2).Value = Now
End Sub

[ This Message was edited by: TsTom on 2002-03-25 15:26 ]

Judy · Mar 25th, 2002, 06:01 PM

Thanks for that, I haven't worked with events before, but I will work out what I need to do there and let you know.
thanks
Judy

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 25th, 2002, 03:02 PM	#1
Judy New Member Join Date: Mar 2002 Posts: 3	I want to scan the students (they have barcode stickers) in our cross country event when they come back across the line. I want excel to get the barcode and grab the system time and correspond it to the barcode. I don't want to use a macro as that is a keystroke after the scan. I can do it with a function using NOW() but every time I add another entry it updates all the times, volatile. How can I get to keep the time it first gets instead of updating all the time?

Mar 25th, 2002, 03:33 PM	#2
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Please give some more detail as to how this information is being entered. Which cells? By way of DDE? Formulas, as far as I know, cannot be made static. I could be wrong about that... A simple event capture would be fast enough. Perhaps just as fast as a function. If not as fast the time involved would be miniscule. Please post more information. Tom

Mar 25th, 2002, 04:04 PM	#3
Judy New Member Join Date: Mar 2002 Posts: 3	I scan the barcode at cell A2 and the barcode number goes in there. The B column has the following =IF(A2>0,NOW(),"")copied down to all cells. So when A2 has a value in it B2 gets the time. After the scan for A2, A3 cell is selected and ready for the next scan. Once cell A3 has a value in it B3 gets the time, but it also updates the previous time in A2 which is where the problem lies. Hope I have explained that little better.

Mar 25th, 2002, 04:09 PM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	place this in your sheet_change event If Target.Column = 1 Then _ Cells(Target.Row, 2).Value = Now

Mar 25th, 2002, 04:14 PM	#5
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Actually, disreguard that non-solution and try this: The other way involves a keystroke Place a formula in AA1 =A1 Copy down Col AA When the info is scanned into A1 the calculation will raise an event which will immediately place now in B Code should look like this Private Sub Worksheet_Calculate() If ActiveCell.Column = 1 Then _ Cells(ActiveCell.Row, 2).Value = Now End Sub try it out Works with DDE, queries I just created a workbook using real time and ran code based upon the calculate event. Tom PS You mentioned that it scrolled down automatically to the next row? You may need to use this code in place of the other Try both Private Sub Worksheet_Calculate() If ActiveCell.Column = 1 Then _ Cells(ActiveCell.Row - 1, 2).Value = Now End Sub [ This Message was edited by: TsTom on 2002-03-25 15:26 ]

Mar 25th, 2002, 06:01 PM	#6
Judy New Member Join Date: Mar 2002 Posts: 3	Thanks for that, I haven't worked with events before, but I will work out what I need to do there and let you know. thanks Judy