Timestamping Cell Entries

[g_erhard]

We distribute a worksheet template containing a 'daily workload' for our employees. As part of that workload they have to paste a value into a previously empty cell (for example, say the empty values are all in column B).

Is there a way to have the worksheet add a timestamp (say, in column F) when a value is FIRST entered in column B? We don't need that timestamp recalculated, so the NOW() function by itself won't work.

Thanks!

[ This Message was edited by: g_erhard on 2002-03-11 08:23 ]

[JohnJay]

You can try the Workbook_SheetChange Sub and adding code that checks if the value you just entered is in column B, if it is, then check if there is a value already located in Column F. If not, then place the time in it. otherwise just drop out of the code...Hope this helps.

[g_erhard]

I'll have to check that out. Thanks.

[Mark W.]

Before you get too deep into creating custom code take a look at the Excel Help topics for "Shared workbooks" and "tracking changes".

[g_erhard]

Not sure that those types of features would help us.

For one thing, sharing the workbooks will impose quite a few limits on what people can do with them, which won't pose a problem for the production crews but WILL pose interesting challenges for our QA teams and managers.

The other problem is this: the Track Changes features are quite obvious when they pop up on a worksheet. Our production agents may be tempted to 'play' with them instead of continuing on with their workload. We would like to instead implement timestamps in such a way that it won't get in the way of production, but rather be a form of behind-the-scenes auditing.

Hopefully that makes sense...

[g_erhard]

OK, figured it out... thanks for the heads-up on the Worksheet_Change event. Here's the code, for any that are interested:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)



Dim scan As Range

Dim stampthis As Range



For Each scan In Target

If (scan.Column = 1) Then

   Set stampthis = scan.Cells(1, 50)

   If scan.Cells(1, 1).Value <> "" Then

   stampthis.Value = Format(Time, "hh:mm:ss")

   End If

End If

Next scan



End Sub

---

Algo is pretty simple:

define the scan range and range to be timestamped. When the event triggers, it passes back the range of the cell(s) that were updated.

A for/next loop is set to determine two things:



For each cell in the "changed" range:

2. is the changed cell in a specific column (in this case, column A).
   
   
3. does the changed cell have anything in it?

If both conditions are satisfied, then a cell 50 cells to the right gets a timestamp written to it.



Go check the next cell in the range and then exit.



Because the event usually triggers on one cell at a time (it's unlikely that more than on cell will have it's "hard" contents updated at once) the routine runs very quickly. The for/next loop is there on the off chance someone triggers a macro that changes more than one cell at the same time.

[ This Message was edited by: g_erhard on 2002-03-20 12:32 ]