Time Stamps for input rows

The Problem

The most recent question from Allen's readers is how to put a time-stamp in a dispatcher's log.  Specifically, the subscriber (Donna) asked for advice on how to a time-stamp next to data she puts into a worksheet while talking to drivers on the radio.,

Following is my highly-edited and emended response) to Allen.

Answering the question... and no more

The following code will work no matter where someone makes an entry in the worksheet, like some people use scratch paper.

Private Sub Worksheet_Change(ByVal Target As Range)    

    Target.Offsett, (0,1) = Format(Now(), "hh:nn:ss")

End Sub

At the end of the shift the dispatcher can organize the notes into a log of calls.

Assuming better organization

Assuming Donna is entering the information from the radio on column A and the time stamp should be in Column B, here is the code.  

Private Sub Worksheet_Change(ByVal Target As Range)

    Const FirstInputRowNum As Integer = 2 ' Prevents overwriting column headers;

    Const InputColNum As Integer = 2

    Const TimeStampColNum As Integer = 1

    If Target.Row >= FirstInputRowNum And Target.Column = InputColNum Then

        If Len(Cells(Target.Row, TimeStampColNum).Text) = 0 Then  ' This prevents later edits from updating the time stamp.

            Cells(Target.Row, TimeStampColNum) = Format(Now(), "hh:nn:ss")

        End If

    End If

End Sub

To change the columns for inputs and time-stamps change the values of InputColNum and TimeStampColNum.  The code allows them to be as far apart as she wishes, and either to the left or right of each other.

Planning for more information

All of that bis fine and dandy, but it does not take into account that organizations often want multiple columns of information.  In the case of the radio calls to the dispatcher, the information may include the vehicle, it's destination, and the driver calling in. The following allows for this expansion and additional flexibility:

Private Sub Worksheet_Change(ByVal Target As Range)

    Const LeftMostInputColNum As Integer = 1

    Const RightMosInputColNum As Integer = 1

    Const TimeStampColNum As Integer = 2

    Const FirstInputRowNum As Integer = 1

    If Target.Column >= LeftMostInputColNum _

            And Target.Column <= RightMosInputColNum _

            And Target.Column <> TimeStampColNum Then

        If Len(Cells(Target.Row, TimeStampColNum)) = 0 Then

            Cells(Target.Row, TimeStampColNum) = Format(Now(), "hh:nn:ss")

        End If

    End If

End Sub

• As coded, it can be used as a directly replacement for the More Organization solution, type in Column A to get a time-stamp in Column B.
• If Donna and her company decide the worksheet should have multiple columns of discrete data (e.g. columns for caller, driver name, vehicle ID, destination) then inputs can be in a range of columns, allowing the dispatcher to begin with any of the data that is usually captured
• The time-stamp can be in any column, i.e. (a) between the leftmost and rightmost input columns, (b) to the left of the leftmost input column, or (c) to the right of the rightmost. or outside

Other Considerations

When implementing an activity log, it would probably be best to

• Lock any cell outside the range for inputs
• Order the columns in the most likely order of entry
• Set the Application.MoveAfterReturnDirection to xlRight
• Put the focus on the assumed first cell for input

Some nice-to-have features might include

• Automatically adding the user's name (see Environmental Variables, Part 1: Accessing the Info) by including the following code
  Const UserNameColNum As Integer = <x> 
  Followed by one of these lines in the areas
  Cells(Target.Row, UserNameColNum ) = Environ("UserName") ' This gives the login ID
  or
  Cells(Target.Row, UserNameColNum ) = Application.UserName ' This gives the User Name that is in Excel
• Locking a row once it is complete.  The single could be simply going to the next row or a time-event
• Allowing for edits of locked rows.  This can be enabled via an icon on the Ribbon, a worksheet control, executing a macro using be accessed through  a worksheet control, or a Context menu item