Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Getting System Time

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    place this in your sheet_change event

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

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •